VBA: IF formula equal to row length

rokeshi99

New Member
Joined
Aug 30, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,856
Office Version
  1. 2010
Platform
  1. Windows
Hi all. I have the code:

///

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

///
Try this:

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

rokeshi99

New Member
Joined
Aug 30, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,980
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

VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,277
Messages
5,635,242
Members
416,848
Latest member
Excel DG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top