VBAto calculate formula and copy formula down to last used Row

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Need little help to get over this hump. I have the below VBA to calculate results for my cells. but i need it to do it for all rows down when row A is used. if row A goes to 10,000. and I want excel to calculate I3-R3 in VBA but do it all the way to I10,000-R10,000 or wherever A ends.

VBA Code:
'Fomulas for Review (S File)
With Workbooks("Compare").Sheets("Periodic")
.Range("I3").Value = .[IFERROR(VLOOKUP(A3,'Table'!$F:$K,4,FALSE),"")]
.Range("J3").Value = .[IFERROR(VLOOKUP(A3,'Table'!$F:$K,5,FALSE),"")]
.Range("L3").Value = .[IF(I3<>"",B3,"")]
.Range("M3").Value = .[IF(I3<>"",C3,"")]
.Range("N3").Value = .[IF(I3<>"",D3,"")]
.Range("O3").Value = .[IF(I3<>"",E3,"")]
.Range("P3").Value = .[IF(I3<>"",F3,"")]
.Range("Q3").Value = .[IF(I3<>"",G3,"")]
.Range("R3").Value = .[IF(I3<>"",H3,"")]
.Columns("A:R").EntireColumn.AutoFit
End With

Before I was doing this and not by any means a fan it took time for the VBA to run. and i had to keep the formulas in the actual cell so could never fully clear my sheet. I just don't know how to combine the top and bottom ones to accomplish what I ultimately need
Code:
'Paste formulas down
UsdRws = Sheets("Periodic").Range("A" & rows.count).End(xlUp).row
If UsdRws > 3 Then Sheets("Periodic").Range("I3:R" & UsdRws).FillDown
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
MAybe this
VBA Code:
Sub MM1()
Dim lr As Long
With Workbooks("Compare").Sheets("Periodic")
lr = Cells(Rows.Count, "A").End(xlUp).Row
With .Range("I3:I" & lr)
.Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,4,FALSE),"""")"
.Value = .Value
End With
With .Range("J3:J" & lr)
.Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,5,FALSE),"""")"
.Value = .Value
End With
.Range("L3").Value = .[IF(I3<>"",B3,"")]
.Range("M3").Value = .[IF(I3<>"",C3,"")]
.Range("N3").Value = .[IF(I3<>"",D3,"")]
.Range("O3").Value = .[IF(I3<>"",E3,"")]
.Range("P3").Value = .[IF(I3<>"",F3,"")]
.Range("Q3").Value = .[IF(I3<>"",G3,"")]
.Range("R3").Value = .[IF(I3<>"",H3,"")]
.Columns("A:R").EntireColumn.AutoFit
End With
End Sub
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
MAybe this
VBA Code:
Sub MM1()
Dim lr As Long
With Workbooks("Compare").Sheets("Periodic")
lr = Cells(Rows.Count, "A").End(xlUp).Row
With .Range("I3:I" & lr)
.Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,4,FALSE),"""")"
.Value = .Value
End With
With .Range("J3:J" & lr)
.Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,5,FALSE),"""")"
.Value = .Value
End With
.Range("L3").Value = .[IF(I3<>"",B3,"")]
.Range("M3").Value = .[IF(I3<>"",C3,"")]
.Range("N3").Value = .[IF(I3<>"",D3,"")]
.Range("O3").Value = .[IF(I3<>"",E3,"")]
.Range("P3").Value = .[IF(I3<>"",F3,"")]
.Range("Q3").Value = .[IF(I3<>"",G3,"")]
.Range("R3").Value = .[IF(I3<>"",H3,"")]
.Columns("A:R").EntireColumn.AutoFit
End With
End Sub
would i need to apply this for all my cells I-R? So one for each. or does L3-R3 become dynamic

VBA Code:
With .Range("I3:I" & lr)
.Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,4,FALSE),"""")"
.Value = .Value
End With
With .Range("J3:J" & lr)
.Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,5,FALSE),"""")"
.Value = .Value
End With
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, try using
VBA Code:
Sub MM1()
Dim lr As Long
With Workbooks("Compare").Sheets("Periodic")
lr = Cells(Rows.Count, "A").End(xlUp).Row
.Range("I3:I" & lr).Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,4,FALSE),"""")"
.Range("J3:J" & lr).Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,5,FALSE),"""")"
.Range("L3:L" & lr).Formula = "=IF(I3<>"""",B3,"""")"
.Range("M3:M" & lr).Formula = "=IF(I3<>"""",C3,"""")"
.Range("N3:N" & lr).Formula = "=IF(I3<>"""",D3,"""")"
.Range("O3:O" & lr).Formula = "=IF(I3<>"""",E3,"""")"
.Range("P3:P" & lr).Formula = "=IF(I3<>"""",F3,"""")"
.Range("Q3:Q" & lr).Formula = "=IF(I3<>"""",G3,"""")"
.Range("R3:R" & lr).Formula = "=IF(I3<>"""",H3,"""")"
.Columns("A:R").EntireColumn.AutoFit
.Range("I3:R" & lr).Value = .Range("I3:R" & lr).Value
End With
End Sub
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Ok, try using
VBA Code:
Sub MM1()
Dim lr As Long
With Workbooks("Compare").Sheets("Periodic")
lr = Cells(Rows.Count, "A").End(xlUp).Row
.Range("I3:I" & lr).Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,4,FALSE),"""")"
.Range("J3:J" & lr).Formula = "=IFERROR(VLOOKUP(A3,'Table'!$F:$K,5,FALSE),"""")"
.Range("L3:L" & lr).Formula = "=IF(I3<>"""",B3,"""")"
.Range("M3:M" & lr).Formula = "=IF(I3<>"""",C3,"""")"
.Range("N3:N" & lr).Formula = "=IF(I3<>"""",D3,"""")"
.Range("O3:O" & lr).Formula = "=IF(I3<>"""",E3,"""")"
.Range("P3:P" & lr).Formula = "=IF(I3<>"""",F3,"""")"
.Range("Q3:Q" & lr).Formula = "=IF(I3<>"""",G3,"""")"
.Range("R3:R" & lr).Formula = "=IF(I3<>"""",H3,"""")"
.Columns("A:R").EntireColumn.AutoFit
.Range("I3:R" & lr).Value = .Range("I3:R" & lr).Value
End With
End Sub
So this will put the formula in the cell and then using the last condition override it with the values? VBA isn't really calculating the formula anymore?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So this will put the formula in the cell and then using the last condition override it with the values?
True, once the formula has done the calculation, the results will become values !
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

True, once the formula has done the calculation, the results will become values !
Ok trying to learn the sequence of events. is there not a way to accomplish what you have but it just puts the value in? Or is this the best way to accomplish that
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hey Mike one issue I am seeing it seems LR isn't enclosed in the WITH statement so it isn't work. Also, it appears if i set a cell to blank then use the below the formulas dont yield results but if i remove the below the formulas themselves work.

VBA Code:
.Range("I3:R" & lr).Value = .Range("I3:R" & lr).Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,273
Office Version
  1. 365
Platform
  1. Windows
There's a slight typo in Michael's code, you need to put a period infront of Cells on this line
VBA Code:
lr = Cells(Rows.Count, "A").End(xlUp).Row
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Ok I will make the necessary modification. Still seeing the below issue though. if I remove the below VBA data will populate correctly with the formulas:

VBA Code:
.Range("I3:R" & lr).Value = .Range("I3:R" & lr).Value

This is my current vba where i just want excel to calculate the formula and give me the result, if I leave like this no data shows until column T to Y i think it has something to do with blanks but I dont know why we set LR to read D where there isnt blanks:

Code:
'Fomulas for Review (N Compare)
With Workbooks("N Compare").Sheets("Compare")
lr = .Cells(rows.count, "D").End(xlUp).row
.Range("L13:L" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),2,0),""YYYYMMDD"")),"""")"
.Range("M13:M" & lr).Formula = "=IFERROR(VALUE(VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),3,0)),"""")"
.Range("N13:N" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),19,0),""YYYYMMDD"")),"""")"
.Range("O13:O" & lr).Formula = "=IFERROR(VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),22,0),"""")"
.Range("P13:P" & lr).Formula = "=IF(ISERROR(VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),23,0)),,VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),23,0))"
.Range("Q13:Q" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),17,0),""YYYYMMDD"")),"""")"
.Range("R13:R" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),18,0),""YYYYMMDD"")),"""")"
.Range("S13:S" & lr).Formula = "=IFERROR(VLOOKUP(D13,IF(F13=""IN"",'N DV'!$A$6:$Y$10000,'N DT'!$A$6:$Y$10000),8,0),"""")"
.Range("T13:T" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(E13=N13,""OK"",""CHECK""))"
.Range("U13:U" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(CONCATENATE(F13,O13)=""IND"",""OK"",""CHECK""))"
.Range("V13:V" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",(G13-P13))"
.Range("W13:W" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(H13=Q13,""OK"",""CHECK""))"
.Range("X13:X" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(I13=R13,""OK"",""CHECK""))"
.Range("Y13:Y" & lr).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(K13=S13,""OK"",""CHECK""))"
'.Range("L13:Y" & lr).Value = .Range("L13:Y" & lr).Value
.Range("12:12").AutoFilter Field:=4, Criteria1:="<>"
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,123,275
Messages
5,600,678
Members
414,400
Latest member
Damocles2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top