VBAto calculate formula and copy formula down to last used Row

I

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
758
Office Version
  1. 365
  2. 2019
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
 
Upvote 0
Michael M said:
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
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
Michael M said:
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
Click to expand...
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?
 
Upvote 0
Michael M said:
True, once the formula has done the calculation, the results will become values !
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Detect if a value goes down in a row of numbers, but not if there is a zero
Replies
8
Views
114
Peter_SSs
Peter_SSs
I
  • Question
Rows counts issue in VBA
Replies
3
Views
330
johnnyL
J
HappyChappy
  • Solved
tidying up formulas
Replies
4
Views
129
HappyChappy
HappyChappy
I
  • Question
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
241
Peter_SSs
Peter_SSs
I
  • Question
Move multiple rows from one table to another using conditional trigger
Replies
2
Views
445
ItsNursinTime
I

Forum statistics

Threads
1,214,402
Messages
6,119,304
Members
448,886
Latest member
GBCTeacher

Share this page

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
Back
Top