Convert formula to VBA

[alwaysXcel]

I tried converting this formula to VBA but am not getting anywhere. Any help would be greatly appreciated!!!!!!!

=IF(ISERROR((LOOKUP(10^308,--MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($E10,XXX,2,FALSE),$B$386,REPT(" ",100)),$B$391,"$$"),100, _
ROW(INDIRECT("1:100")))))),0,(LOOKUP(10^308,--MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($E10,XXX,2,FALSE),$B$386,REPT(" ",100)),$B$391,"$$"),100,ROW(INDIRECT("1:100"))))))

XXX is a defined range name

[austenr]

May I ask why you want to do this in VBA? Do you just want a macro to execute this?

[alwaysXcel]

Well I have this formula for about 1000 * 5 columns cells and this results in the file size becoming extremly huge. Therefore, I just want to convert this into VBA so that I can paste it and have the macro execute it and then copy/paste values.

[austenr]

Your formula may take a bit of reworking to get it to work. Really do not have time right now but I'm sure someone will be along in a while to lend a hand.

[Zack Barresse]

Well, you could use the Evaluate method to return the answer. Then step through each required cell with a small loop, copying/pasting values as you go. If you would like your formula reworked (as it is quite monstrous - lord knows I've had mine) post an example spreadsheet, it most likely can be revised.

[Jacob Hilderbrand]

The first thing we need to do is break this formula down and use two cells.

A1=IF(ISERROR(B1),0,B1)
B1=LOOKUP(10^308,--MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($E10,XXX,2,FALSE ),$B$386,REPT(" ",100)),$B$391,"$$"),100,ROW(INDIRECT("1:100"))))

[Jacob Hilderbrand]

Now if you want to use VBA we can use the Evaluate method like Zack suggested.

If IsError(Evaluate("LOOKUP(10^308,--MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($E10," & _
    "XXX,2,FALSE   ),$B$386,REPT(" & """" & " &""""" & ",100)),$B$391," & """" & _
    "$$" & """" & "),100,ROW(INDIRECT(" & """" & "1:100" & """" & "))))")) Then
    Val1 = "N/A"
Else
    Val1 = Evaluate("LOOKUP(10^308,--MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($E10," & _
    "XXX,2,FALSE   ),$B$386,REPT(" & """" & " &""""" & ",100)),$B$391," & """" & _
    "$$" & """" & "),100,ROW(INDIRECT(" & """" & "1:100" & """" & "))))")
End If

Now that you have Val1 you can use it in the rest of your code or you can put it in a cell or whatever.

If you need more help can you post an attachment and also let us know what you are trying to do.

[Ivan F Moala]

[E5] = "=IF(ISERROR((LOOKUP(10^308,--MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($E10,XXX,2,FALSE )," & _
    "$B$386,REPT("" "",100)),$B$391,""$$""),100,ROW(INDIRECT(""1:100"")))))),0," & _
    "(LOOKUP(10^308,--MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP($E10,XXX,2,FALSE )," & _
    "$B$386,REPT("" "",100)),$B$391,""$$""),100,ROW(INDIRECT(""1:100""))))))"

[herilane]

The macro doesn't need to know anything about the formula.
The easiest fix, in my opinion, would be to keep the formula in one of your 5000 cells, and convert the other cells to values. Whenever you want to update the calculations, copy the formula to all the other 4999 cells, calculate, and convert formulas to values (copy, paste special as values). This copying & pasting can be done manually or with a macro, whichever you prefer - but this approach would keep the original formula visible/transparent, and easily accessible for editing.

[alwaysXcel]

Thank you for your suggestions. The reason I also want to keep this in VBA is that I don't want other users to see the formulas. I tried using the evaluate method and this seems to have been working. Thank you very much for all your help!!!!!

[Jacob Hilderbrand]

You're Welcome

Take Care

P.S. Using the Evaluate method on 5000 cells might be rather slow. If that is the case we can have the macro place the formula in one cell, then Auto Fill for all the cells, then Cope/Paste Special Values.