excel formula within vba 1004 error

Question

I am comparing two columns and displaying the differences in another column. I have an excel formula that works but I need to use it in a vba macro. When I try to usie it in the macro though I get the error "Run-time Error: '1004': Application-defined or object-defined error". How can this be fixed?

Sub Macro2()

   Range("B2:B60").Formula = "=IF(COUNTIF($A:$A,$B2)=0, B2, "")"

End Sub

YowE3K · Accepted Answer · 2016-10-04 08:17:38Z

3

Quotation marks within a string need to be doubled-up so, instead of

Range("B2:B60").Formula = "=IF(COUNTIF($A:$A,$B2)=0, B2, "")"

use

Range("B2:B60").Formula = "=IF(COUNTIF($A:$A,$B2)=0, B2, """")"

but please note that this will give a circular reference error, as cell B2 (etc) will be referencing itself.

Maybe you are intending to write the new value to column C? If so, use

Range("C2:C60").Formula = "=IF(COUNTIF($A:$A,$B2)=0, B2, """")"

edited Oct 4, 2016 at 8:17

answered Oct 4, 2016 at 8:10

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kit Over a year ago

Thanks, this just changes the B column to 0's. Do you know of a ways a way around this? In excel the formula works as expected.

YowE3K Over a year ago

@kit - the code just writes the formula into the Excel cell, so if you entered the formula in Excel manually you would also get a circular reference error.

Collectives™ on Stack Overflow

excel formula within vba 1004 error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related