VBA: IF formula equal to row length

[rokeshi99]

Hi all. I have the code:

///

.range("a1:a100").specialcells(xlcelltypevisible).formula = "=if(c3=""2","""",d3)"

///

this puts that formula into my visible cells. but the thing is, the first visible cell will be equal to that formula exactly. for example, if my first visible cell is a50, it will still say =if(c3="","",d3). is there a way to make it say =if(c50="","",d50) instead? so a way of setting x to row length?

Thanks.

 

[JLGWhiz]

Dim rng As Range
Set rng = Range("a1:a100").SpecialCells(xlCellTypeVisible)
    rng.Formula = "=if(" & rng.Cells(1, 3).Address(False, False) & "=2,""""," & rng.Cells(1, 4).Address(False, False) & ")"

 

[yky]

Hi all. I have the code:

///

.range("a1:a100").specialcells(xlcelltypevisible).formula = "=if(c3=""2","""",d3)"

///

Try this:

.range("a1:a100").specialcells(xlcelltypevisible).formula = "=if(c3=2,"""",d3)"

 

[rokeshi99]

Dim rng As Range
Set rng = Range("a1:a100").SpecialCells(xlCellTypeVisible)
    rng.Formula = "=if(" & rng.Cells(1, 3).Address(False, False) & "=2,""""," & rng.Cells(1, 4).Address(False, False) & ")"

ty! worked perfectly

 

[mikerickson]

What did you think of my reply to your post at

https://www.reddit.com/r/excel/comments/ijm000

12 hours ago I suggested the you use R1C1 notation

range("a1:a100").specialcells(xlcelltypevisible).formula = "=if(RC[2]=""2","""",RC[3])"

Did that not work for you?
Also, which of your formulas do you want to use the =2 formula in the VB code you posted or the ="" that you typed in the text portion?

 

[JLGWhiz]

ty! worked perfectly

You're welcome, but you should have noted in your OP that you were cross posting on another web site and provide a link to that site.
Regards, JLG