ItalianPlatinum
Well-known Member
- Joined
- Mar 23, 2017
- Messages
- 776
- Office Version
- 365
- 2019
- Platform
- 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.
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
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