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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,281
Office Version
  1. 365
Platform
  1. Windows
Will the cells in col D always have a value for every row?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,281
Office Version
  1. 365
Platform
  1. Windows
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.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,281
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Calculation will need to be on automatic & make sure that you do not disable calculation within the code.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,281
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,281
Office Version
  1. 365
Platform
  1. Windows
No just that line as is.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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