Formula to vba conversion

[lesliewheeler]

Hi

I have the below 2 formulas

formula in cell D3 =VLOOKUP(B3,$R$1:$T$37,2,0) - This is pasted in cell D3 right down to D1000001

formula in cell E3 =IF(B3=0,"LOSS",IF(OR(VALUE(D3)=(VALUE(LEFT(C2,1))),VALUE(D3)=VALUE(RIGHT(C2,1))),"HIT","LOSS"))- This is pasted in cell E3 right down to E1000001

Can anyone help me with the VBA code for these 2 formula.

Thank You.

 

[jmacleary]

Hi there. A really low-tech but effective solution is: record a macro filling down from row 3 to row 10000001, and then run it each time you need it

 

[lesliewheeler]

Hi there. A really low-tech but effective solution is: record a macro filling down from row 3 to row 10000001, and then run it each time you need it

This pastes the formula copied in macro recording, so it wont solve the purpose as the file is getting heavy due to formulas

 

[Joe4]

This pastes the formula copied in macro recording, so it wont solve the purpose as the file is getting heavy due to formulas

I see "VLOOKUP" and the mention of a lot of data, and it makes me believe that we may actually be dealing with a Relational Database.
If that is indeed the case, while you can try to use Excel for something like that, it can be rather clunky and inefficient (as you see).
Excel really wasn't designed for that purpose, so if you are truly trying to improve efficiency, it may be better to try to use a Relational Database program, such as Microsoft Access, SQL, or MySQL.

If using one of those tools is not an option for you, then perhaps you can do something like what jmacleary suggested, and after you run the code to insert those formulas, you can have your VBA code copy over the formulas with the values of those calculations (to "hard-code" the data). That will get rid of all those calculations after you calculate them, and should reduce the overhead.