VBA Execute excel formula with cell reference in many different cells

Question

I have an excel workbook of +40Mb. Reason is several sheets that use formulas over a range of 30 columns and 20000 rows. I want VBA to calculate these cells in stead the 600000 formulas.

What I need is a VBA script to calculate to following: Column A row 2:

"=IFERROR(IF(MATCH(Sheet1!G3;Sheet2!K3:AE3;0);1;0);0)"

Column A row 3:

"=IFERROR(IF(MATCH(Sheet1!G3;Sheet2!K4:AE4;0);1;0);0)"

... Column A row 20000:

"=IFERROR(IF(MATCH(Sheet1!G3;Sheet2!K20001:AE20001;0);1;0);0)"

Column B row 2:

"=IFERROR(IF(MATCH(Sheet1!G4;Sheet2!K3:AE3;0);1;0);0)"

Column B row 3:

"=IFERROR(IF(MATCH(Sheet1!G4;Sheet2!K4:AE4;0);1;0);0)"

... Column B row 20000:

"=IFERROR(IF(MATCH(Sheet1!G4;Sheet2!K20001:AE20001;0);1;0);0)"

And so on for the other columns.

Can anyone help with this? I'm a newbie to VBA and have no clue where to start.

Many thanx in advance!

First question - Why are you moving this to VBA? Is it due to the size of the sheet or that calculation takes very long? Because if it's just that calculation takes long, you might be able to do something simpler than a full VBA solution... — John Bustos

John Bustos John Bustos · Accepted Answer · 2014-03-25T15:27:45

Given what you requested, I think your easier solution would be to still let Excel do the heavy lifting, but not keep it as Excel formulas once you're done (ie convert it to values).

That being the case, this should do it:

Sub PutInFormulas()

    ActiveSheet.Range("A2").Formula = "=IFERROR(IF(MATCH(OFFSET(Sheet1!$G$2;COLUMN();0);Sheet2!$K3:$AE3;0);1;0);0)"
    ActiveSheet.Range("A2").Copy ActiveSheet.Range("A2:AD20000")
    ActiveSheet.Range("A2:AD20000").Value = ActiveSheet.Range("A2:AD20000").Value

End Sub

Basically, your first step is to make it a formula you can paste down and across, so you replace your Sheet1!G3 with an Offset() reference that increments one row for each column you move across (In other words, any formula in Column A will reference G3, column B will reference G4, etc.) and to put that formula in cell A2

Next you copy it across to your entire range

Finally your convert the formulas to values.

Hope this makes sense / does the trick!!

VBA Execute excel formula with cell reference in many different cells

1 Answers