Any Excel experts in VBA?

[robinessex]

I'm trying to write a VBA function that will create a megaformula from any cell that contains a formula being fed from other cells with formulas in them

[paulrockliffe]

It's just writing a value to a cell and some concatenation so Google some of that.

[robinessex]

[quote=paulrockliffe]It's just writing a value to a cell and some concatenation so Google some of that.[/quote

I have, it's not as simple as that. It's easy to manually just using Cntrl C and Cntrt. V. Automating it with VBA so it'll work on any cell with a formula in it is a bit more involved. I've two books on VBA programming, sussed most of it out, just one bit I'm stuck on.

[Baldchap]

robinessex]aulrockliffe said:

It's just writing a value to a cell and some concatenation so Google some of that.[/quote

I have, it's not as simple as that. It's easy to manually just using Cntrl C and Cntrt. V. Automating it with VBA so it'll work on any cell with a formula in it is a bit more involved. I've two books on VBA programming, sussed most of it out, just one bit I'm stuck on.

Maybe tell us what that is.

[paulrockliffe]

Sounds like your issue is the circumstances where you want to apply your function? Do you want to be able to select a cell, press a button and have a new formula popup somewhere else or something along those lines?

It sounds a lot like a function, perhaps you can do this by creating a custom function instead? Then you would simply create the Formula =FUNCTION(A1) in whatever cell and it would feed the formula in A1 into your function and give you your new formula result that way.

I think if you've got VBA books and vaguely know what you're doing then you're going to need to describe the circumstances in a bit more detail to get a useful answer.

[robinessex]

I've been playing around with creating a spreadsheet to solve Sudoku puzzles. The formulas to solve the logic are relatively simple, there's just a bloody huge amount of them. I've manually made 'megaformulas' in some cells by simply replacing the cell i.d. in the formula with the formula that the cell contains. However, there are vastly to many cells to do that manually, so I'd like to select a cell and run a Macro that would substitute the formulas contained in the cell reference. Thus, if I had a simple formula in a cell, "=A1+B1", the Macro would substitute the formula that's in cells A1 & B1. The actual formulas reference multiple cells, so it would have to loop until all the reference cells have been replaced.

Actual cell formula from Sudoku spreadsheet:-

This :-

=IF(AN58=FALSE,AN31,IF(BO58=FALSE,AN31,IF(BO31=FALSE,AN31, )))

Becomes this with formulas replacing cell references:-

=IF(IF(AN31=AN28,TRUE,IF(AN31=AN34,TRUE,IF(AN31=AN37,TRUE,IF(AN31=AN40,TRUE,IF(AN31=AN43,TRUE,IF(AN31=AN46,TRUE,IF(AN31=AN49,TRUE,IF(AN31=AN52,TRUE,FALSE))))))))=FALSE,AN31,IF(IF(AN31=AK28,TRUE,IF(AN31=AN28,TRUE,IF(AN31=AQ28,TRUE,IF(AN31=AK31,TRUE,IF(AN31=AQ31,TRUE,IF(AN31=AK34,TRUE,IF(AN31=AN34,TRUE,IF(AN31=AQ34,TRUE,FALSE))))))))=FALSE,AN31,IF(IF(AN31=AB31,TRUE,IF(AN31=AE31,TRUE,IF(AN31=AH31,TRUE,IF(AN31=AK31,TRUE,IF(AN31=AQ31,TRUE,IF(AN31=AT31,TRUE,IF(AN31=AW31,TRUE,IF(AN31=AZ31,TRUE,FALSE))))))))=FALSE,AN31, )))

Edited by robinessex on Thursday 30th June 09:36

[paulrockliffe]

I see, so you're trying to build a formula that solves each cell based on the cells that are completed and the other cells that have been worked out?

My instinct is that you also need to control the order that the cells are resolved because if you don't circular references are inevitable in your formulas.

Or have you already got that and you're using VBA to then add the formulas in the correct order?

I don't know what the answer is without spending far too long on it, but I would consider using the Solver Add-in as that will iterate, which is really what you're trying to do. For each cell you want to test whether it is solved already, then if it can be solved in this iteration, then if yes solve it, repeat for all cells. Then repeat the process.

Cleverer people than me could probably model the Sudoku rules to be much more fundamental than the way people do it in their heads to feed into Solver too. Turn it into more of a Brute-force approach.

[robinessex]

paulrockliffe said:

I see, so you're trying to build a formula that solves each cell based on the cells that are completed and the other cells that have been worked out?

My instinct is that you also need to control the order that the cells are resolved because if you don't circular references are inevitable in your formulas.

Or have you already got that and you're using VBA to then add the formulas in the correct order?

I don't know what the answer is without spending far too long on it, but I would consider using the Solver Add-in as that will iterate, which is really what you're trying to do. For each cell you want to test whether it is solved already, then if it can be solved in this iteration, then if yes solve it, repeat for all cells. Then repeat the process.

Cleverer people than me could probably model the Sudoku rules to be much more fundamental than the way people do it in their heads to feed into Solver too. Turn it into more of a Brute-force approach.

Basically, I'm manually progressing through the 'simple' formula, and substituting formulas for cells. It works if done manually, so I'm looking for a macro to do the same thing. Took about 1 week to go through one sheet manually, but it worked ok when I'd finished.

[vaud]

VBA and nonVBA approaches covered here:

https://www.microsoft.com/en-us/microsoft-365/blog...

(contains spoilers)

[robinessex]

vaud said:

VBA and nonVBA approaches covered here:

https://www.microsoft.com/en-us/microsoft-365/blog...

(contains spoilers)

Thanks for that. There's quite a lot of info on the web re Suduko Excel Solvers. Typically, I wanted to go my own way, it's more 'fun'. I have used the iterative method for some engineering mathematical calcs in the past.

[robinessex]

vaud said:

VBA and nonVBA approaches covered here:

https://www.microsoft.com/en-us/microsoft-365/blog...

(contains spoilers)

Unfortunately, in its infinite wisdom. Microsoft has deleted all the spreadsheet images in that article, probably because it's dated 2008.

Bingo! After a bit of net searching, think I've found an archived version of the article.

Edited by robinessex on Thursday 30th June 10:57

[Chromegrill]

Googling for other people's VBA is a good start. One reason I live Excel is that it's so widely used, someone somewhere is,almost bound to have published a solution on one of the many fora,,more so than many other programs.

Have you considered concatenating all the options, and doing a vlookup that combines selected options to find the winning entry?