VBAto calculate formula and copy formula down to last used Row

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
733
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2010
  5. 2007
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
 
Will the cells in col D always have a value for every row?
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Yes column D will be what I need formulas for. it may be blank after the data ends. L-S the formula will populate blanks if it meets criteria and I hope that isn't driving it to be wrong
 
Upvote 0
In that case if everything works when you comment out this line
VBA Code:
.Range("L13:Y" & lr).Value = .Range("L13:Y" & lr).Value
It sounds as though the formulae have not finished calculating by the time the macro converts them to values.
 
Upvote 0
Yes exactly. it has the formulas in the cells and works. Just I am trying to not put the formula in the cell. do i need to set the calculation to manual or automatic?
 
Upvote 0
Calculation will need to be on automatic & make sure that you do not disable calculation within the code.
 
Upvote 0
the only items I am seeing are these. everything is set to calculation. I dont get why it only doesn't work for the formulas where I have conditions to apply the cell as blank ("") if something doesn't meet my criteria. the columns that work are set with values. seems oddly coincidental.

VBA Code:
Application.ScreenUpdating = False
Application.EnableEvents = False
 
Upvote 0
You have already said it works for all your formulae, the problem is that the vlookups will take time to calculate & your code maybe converting to values before it has completed.
You can try adding Application.Calculate just before the line that converts to values.
 
Upvote 0
You have already said it works for all your formulae, the problem is that the vlookups will take time to calculate & your code maybe converting to values before it has completed.
You can try adding Application.Calculate just before the line that converts to values.
Do I need to put a period before that so it is included in the with statement or does it not work like that
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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