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

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
554
Office Version
  1. 2016
  2. 2010
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
554
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
There is no error message it just doesnt calculate the formula properly
 

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
554
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
554
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
Anyone else want to take over here? My goldfish is due another piano lesson!
 

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
554
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,400
Messages
5,837,027
Members
430,467
Latest member
FrazzledbyExcel

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