ItalianPlatinum
Wellknown Member
 Joined
 Mar 23, 2017
 Messages
 554
 Office Version

 2016
 2010
 Platform

 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
Works  shows correct formula result
Works  shows correct formula result
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