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

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
686
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2010
  5. 2007
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
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
VBA Code:
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
There is no error message it just doesnt calculate the formula properly
 
Upvote 0
Right hence my original post. it isn't working, calculating correctly.
 
Upvote 0
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"
 
Upvote 0
Anyone else want to take over here? My goldfish is due another piano lesson!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,876
Members
447,882
Latest member
LORENA

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
Back
Top