Two VBA lines essentially identical not showing same results, Why?

[ItalianPlatinum]

Hello - I had a VBA to copy a formula down then insert the value right over it. The first way doesn't properly execute the formula. if I remove copying the values the formula executes correctly so i know the formula is correct. But when I break the VBA into two lines one for column A and one for column B it works! Anyone know why I cant combine like the first way?

Doesn't Work - shows wrong formula result

With Sheets("Calculation")
lr = .Cells(rows.count, "C").End(xlUp).row
    .Range("A3:A" & lr).Formula = "=D3&E3&G3"
    .Range("B3:B" & lr).Formula = "=IF(ISNA(VLOOKUP(A3,'AS'!$A$2:$A$10000,1,FALSE)), ""No"", ""Yes"")"
    .Range("A3:B" & lr).Value = .Range("A3:B" & lr).Value
    .Range("2:2").AutoFilter

Works - shows correct formula result

With Sheets("Calculation")
lr = .Cells(rows.count, "C").End(xlUp).row
    .Range("A3:A" & lr).Formula = "=D3&E3&G3"
    .Range("B3:B" & lr).Formula = "=IF(ISNA(VLOOKUP(A3,'AS'!$A$2:$A$10000,1,FALSE)), ""No"", ""Yes"")"
    .Range("A3:A" & lr).Value = .Range("A3:A" & lr).Value
    .Range("B3:B" & lr).Value = .Range("B3:B" & lr).Value
    .Range("2:2").AutoFilter

Works - shows correct formula result

With Sheets("Calculation")
lr = .Cells(rows.count, "C").End(xlUp).row
    .Range("A3:A" & lr).Formula = "=D3&E3&G3"
    .Range("B3:B" & lr).Formula = "=IF(ISNA(VLOOKUP(A3,'AS'!$A$2:$A$10000,1,FALSE)), ""No"", ""Yes"")"
    .Range("2:2").AutoFilter

 

[johnnyL]

I have looked at this also and I agree, the three code examples are basically the same.

The one exception is the last example that is reported as 'Works', doesn't have the line that removes the formulas, other than that it is the same as the first example that is reported as not working.

 

[Alex Blakenburg]

Just for interest can you try inserting this code before the .value = .value line.

Do Until Application.CalculationState = xlDone
    DoEvents
Loop

 

[ItalianPlatinum]

Like this?

Do Until Application.CalculationState = xlDone
    DoEvents
Loop

With Sheets("Calculation")
lr = .Cells(rows.count, "C").End(xlUp).row
    .Range("A3:A" & lr).Formula = "=D3&E3&G3"
    .Range("B3:B" & lr).Formula = "=IF(ISNA(VLOOKUP(A3,'AS'!$A$2:$A$10000,1,FALSE)), ""No"", ""Yes"")"
    .Range("M3:M" & lr).Formula = "=IF(B3=""No"",""No Change"",IF(AND(J3<>0,K3=0),VLOOKUP(A3,'AS'!$A$2:$J$10000,9,FALSE),""No Change""))"
    .Range("N3:N" & lr).Formula = "=IF(AND(J3=0,K3<>0),0,""No Change"")"
    .Range("A3:B" & lr).Value = .Range("A3:B" & lr).Value
    '.Range("B3:B" & lr).Value = .Range("B3:B" & lr).Value
    .Range("M3:M" & lr).Value = .Range("M3:M" & lr).Value
    .Range("N3:N" & lr).Value = .Range("N3:N" & lr).Value
    .Range("2:2").AutoFilter
    .Activate
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

 

[Alex Blakenburg]

Like this?

No like the below. After entering the formula and before replacing the formula with the result of the formula as a value.
(I was just wondering if the formulas being entered had not finsied calculating by the time the code tried to replace it with values.)

With Sheets("Calculation")
lr = .Cells(rows.count, "C").End(xlUp).row
    .Range("A3:A" & lr).Formula = "=D3&E3&G3"
    .Range("B3:B" & lr).Formula = "=IF(ISNA(VLOOKUP(A3,'AS'!$A$2:$A$10000,1,FALSE)), ""No"", ""Yes"")"
    .Range("M3:M" & lr).Formula = "=IF(B3=""No"",""No Change"",IF(AND(J3<>0,K3=0),VLOOKUP(A3,'AS'!$A$2:$J$10000,9,FALSE),""No Change""))"
    .Range("N3:N" & lr).Formula = "=IF(AND(J3=0,K3<>0),0,""No Change"")"

   ' Ensure that calculations have finished before replacing formulas with values
    Do Until Application.CalculationState = xlDone
       DoEvents
    Loop

    .Range("A3:B" & lr).Value = .Range("A3:B" & lr).Value
    '.Range("B3:B" & lr).Value = .Range("B3:B" & lr).Value
    .Range("M3:M" & lr).Value = .Range("M3:M" & lr).Value
    .Range("N3:N" & lr).Value = .Range("N3:N" & lr).Value
    .Range("2:2").AutoFilter
    .Activate
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

 

[ItalianPlatinum]

It is working today even without your line of code. The only thing that changed from today to yesterday was I restarted my PC / excel. Kind of confused and annoyed. I am going to closely monitor my test results and will repost here if it comes up again while testing your example Alex. Hopefully it doesn't because I didnt quite get what was happening yesterday

 

[Alex Blakenburg]

Thanks for the update. Let us know how you go.

 

[ItalianPlatinum]

I found it. I was setting calculation to manual prior to the code. It resurfaced. so the formulas weren't calculating then pasting values. the code was embedded in another vba i dont own so I am setting it to automatic before and now works. you sort of led me to it Alex. thanks

 

[Alex Blakenburg]

Arghh ?‍, that code I gave you is often proceeded with the line

Application.Calculate

but since you didn't have an

Application.Calculation = xlCalculationAutomatic

at the end with all your other Application reset commands and the fact that your initial code indicated that some of the options were calculating, I thought it superfluous.

Glad you worked it out. If you haven't already you will want to add the below line to the end of your code though.

Application.Calculation = xlCalculationAutomatic