VBAto calculate formula and copy formula down to last used Row

[ItalianPlatinum]

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.

'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

'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]

In that case you could try

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

 

[ItalianPlatinum]

I went all the way up to 45secs and still same issue.

 

[Fluff]

In that case you maybe better off leaving the formulae in the cells & then have another macro that converts them to values.

 

[ItalianPlatinum]

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

'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]

See post#23

 

[ItalianPlatinum]

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]

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

 

[ItalianPlatinum]

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]

You can use something like

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