VBA to insert a formula that uses A1 style fixed column variable row

[sshone1]

Hello, I need some help.

I am looking to have a macro write a formula into a cell using the A1 style. I need to be able write it as $D + Y, where Y is a variable number I have. I can't get the syntax correct. Please see below...

This one works perfectly...
copyWs.Range("B" & Y).Formula = "=IF(" & cName & "!B5=0,""""," & cName & "!B5)"

Now I want to do the same thing but this time I want to multiply B5 at the end of the if by a $D + Y. How do I merge the $D and the Y to become $D4, where y = 4?
copyWs.Range("U" & Y).Formula = "=IF(" & cName & "!B5=0,""""," & cName & "!B5*(""$D"" & Y &)"

Any help would be great.
Thanks

 

[shknbk2]

Try this:

copyWs.Range("U" & Y).Formula = "=IF(" & cName & "!B5=0,""""," & cName & "!B5*$D" & Y & ")"

 

[sshone1]

Thank you very much this worked perfect. I could of sworn that I tried this but I must of missed something. Thanks!