activecell.formula syntax

[Larry Levinson]

ok, I know I SAID I'd never as this question again ... (and I even
looked up my old posts to try to find the answer,) but ...

why is this wrong?

ActiveCell.Formula = "=IF(Contacts!A" & row & "="","",Contacts!A" &
row&")"
Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[John Wilson]

Larry,

Unsure from the way this displays on the screen but possibly all
that's wrong is the spacing ( especially that last "row&")". )
I assume that "row" is a variable??

ActiveCell.Formula = "=IF(Contacts!A" & row & "="","",Contacts!A" & row &
")"

John

 

[Tom Ogilvy]

An easy way to check out these type problems is to use the immediate window
in the VBE:

row = 10
? "=IF(Contacts!A" & row & "="","",Contacts!A" & row & ")"
=IF(Contacts!A10=",",Contacts!A10)

So this indicates there is some type of problem with your double quotes -
they are not coming out as you intended.

This appears to be more what you want:

? "=IF(Contacts!A" & row & "="""","""",Contacts!A" & row & ")"
=IF(Contacts!A10="","",Contacts!A10)


so
ActiveCell.Formula = _
"=IF(Contacts!A" & row & "="""","""",Contacts!A" & row & ")"

When inside a string, to get a single double quote, you need to enter two
double quotes.

 

[COM]

Two problems were identified with your version of the statement. One was that you needed a space following the last reference to Row so that it read Row & ")" vice Row& ")" The other was, that with the way that you were using quotes, if row = 5 then the result would be this "=IF(Contacts!A5=,,Contacts!A5)

Because you wanted to compare Contacts!A5 to an empty string, you need to include Chr(34) (Format code for a quote) to insert the quote into the formula. Your formula should look like the following to get the desired results and not to have an error from the debugger.

ActiveCell.Formula = "=IF(Contacts!A" & Row & "=" & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",Contacts!A" & Row & ")"


----- Larry Levinson wrote: -----

ok, I know I SAID I'd never as this question again ... (and I even
looked up my old posts to try to find the answer,) but ...

why is this wrong?

ActiveCell.Formula = "=IF(Contacts!A" & row & "="","",Contacts!A" &
row&")"
Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)

 

[Larry Levinson]

many thanks. I may bookmark this one.

An easy way to check out these type problems is to use the immediate window
in the VBE:

row = 10
? "=IF(Contacts!A" & row & "="","",Contacts!A" & row & ")"
=IF(Contacts!A10=",",Contacts!A10)

So this indicates there is some type of problem with your double quotes -
they are not coming out as you intended.

This appears to be more what you want:

? "=IF(Contacts!A" & row & "="""","""",Contacts!A" & row & ")"
=IF(Contacts!A10="","",Contacts!A10)


so
ActiveCell.Formula = _
"=IF(Contacts!A" & row & "="""","""",Contacts!A" & row & ")"

When inside a string, to get a single double quote, you need to enter two
double quotes.

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)