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

 

[jasonb75]

Doesn't Work - shows wrong formula result

Should work fine, impossible to say why it's not without seeing the error.

 

[ItalianPlatinum]

There is no error message it just doesnt calculate the formula properly

 

[jasonb75]

it just doesnt calculate the formula properly

If it is not calculating properly then that is an error.

 

[ItalianPlatinum]

Right hence my original post. it isn't working, calculating correctly.

 

[jasonb75]

I can see that this is going to be a slow process.

Doesn't Work - shows wrong formula result

The wrong results are an error, this is what we need to see.

 

[ItalianPlatinum]

Formula: .Range("B3:B" & lr).Formula = "=IF(ISNA(VLOOKUP(A3,'AS'!$A$2:$A$10000,1,FALSE)), ""No"", ""Yes"")"

Incorrect result shows "No"
Correct result shows "Yes"

 

[jasonb75]

Anyone else want to take over here? My goldfish is due another piano lesson!

 

[ItalianPlatinum]

not sure what you are looking for if it is something specific can you just come out and say it? i have already proved the formula is accurate and the data it pulls from is accurate. I segregated my issue down to the core vba line and showed an alternative vba as a workaround that works. I cannot share my file its a huge document with confidential data. If you need a mockup I can surely do so but I guess just not sure what that accomplishes when we know the formula works.

 

[jasonb75]

The line of code that you say doesn't work should work fine. The same method works fine for me. If it is not working for you then there is something either in your document, or in the rest of your code that is causing the problem.

Without seeing the sheet and the problem occurring I think that there is little we can do to isolate the cause.