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,280
Office Version
  1. 365
Platform
  1. Windows
In that case you could try
VBA Code:
Application.Wait (Now + TimeValue("00:00:05"))
Once again add this just before you convert to values. This will put a 5sec delay in the code, you may need to adjust the 05
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,280
Office Version
  1. 365
Platform
  1. Windows
In that case you maybe better off leaving the formulae in the cells & then have another macro that converts them to values.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Alright I think I diagnosed it but dont know what to do now. any formula that could set the cell blank doesnt work, i.e. L-O dont work when value vba line set but P-R works when value line is set

VBA Code:
'Formulas for Review (Compare)
With Workbooks("Compare").Sheets("Compare")
lr = .Cells(rows.count, "D").End(xlUp).row
    .Range("L13:L" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,'DV'!$A$6:$Y$10000,2,0),""YYYYMMDD"")),"""")"
    .Range("M13:M" & lr).Formula = "=IFERROR(VALUE(VLOOKUP(D13,'DV'!$A$6:$Y$10000,3,0)),"""")"
    .Range("N13:N" & lr).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,'DV'!$A$6:$Y$10000,19,0),""YYYYMMDD"")),"""")"
    .Range("O13:O" & lr).Formula = "=IFERROR(VLOOKUP(D13,'DV'!$A$6:$Y$10000,22,0),"""")"
    .Range("P13:P" & lr).Formula = "=IF(ISERROR(VLOOKUP(D13,'DV'!$A$6:$Y$10000,23,0)),,VLOOKUP(D13,'DV'!$A$6:$Y$10000,23,0))"
    .Range("Q13:Q" & lr).Formula = "=0"
    .Range("R13:R" & lr).Formula = "=0"
 

Fluff

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

ADVERTISEMENT

See post#23
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
In that case you maybe better off leaving the formulae in the cells & then have another macro that converts them to values.
But wouldnt I still need to somehow use LR to input values for all rows columns with a formula in it? I guess I am not sure how do to that without incurring the same issue
 

Fluff

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

ADVERTISEMENT

You would calculate LR the same as in your current code.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
393
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
with a dynamic range i would still need to use LR even using another means like you mention. Unless you are saying their is another way and that other way i dont know. for now ill just leave the formulas in there i suppose.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,280
Office Version
  1. 365
Platform
  1. Windows
You can use something like
VBA Code:
With Workbooks("N Compare").Sheets("Compare")
lr = .Cells(Rows.count, "D").End(xlUp).Row
.Range("L13:Y" & lr).Value = .Range("L13:Y" & lr).Value
.Range("12:12").AutoFilter Field:=4, Criteria1:="<>"
End With
& run it as a separate macro after you are happy that your formulae have calculated
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,715
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