Can you add NULL (not Blank "") to an If statement in a formula?

[EssKayKay]

Hello,

I’m just trying something here. Is there a way to add a NULL value to a cell (not “”) via a formula? The reason I ask about this is I believe the NULL option reacts differently than a blank “” entry.

I placed a formula =If(A1<=0,NULL,100) in range B1 and copied down thru B10.
B2 is =If(A2<=0,NULL,100), B3 is =If(A3<=0,NULL,100), . . . B10 is =If(103<=0,NULL,100).

As example only –
Range A1:A5 contains zeros (or some text) and range A6:A10 have values in them. I want to set a range only for the area that has values (i.e., B6:B10).

If I code something like:

   Range(“B10”).Select
   Range(Selection, Selection.End(xlUp)).Select

With a formula using a blank “” option (i.e., If(A1<=0,””,100) the range will be B1:B10. Since B1:B5 appear to be blank but do contain “”. I was hoping if I could insert a NULL into B1:B5 then when I run the Selection.End.(xlUp) process it would only select B10:B6.

Thanks for viewing,
Steve

 

[RajeshKumar1313]

got your point.

 

[MARK858]

@EssKayKay not ideal (both codes will select negative numbers or zero's in column B but by post 1 you will only have 100 or "" in the cells) but what about

Range("B1:B10").SpecialCells(xlCellTypeFormulas, 1).Select

Although as Rory stated you rarely need to select the cells to work with them in VBA so it depends what you are doing with the code afterwards

Book1
	A	B
1	0
2	0
3	dddd	100
4	0
5	0
6	1	100
7	2	100
8	3	100
9	4	100
10	5	100
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=IF(A1<=0,"",100)

Or

    Dim myRng As Range

    Set myRng = Range("A1:A10").SpecialCells(xlCellTypeConstants, 1)
    myRng.Offset(, 1).SpecialCells(xlCellTypeFormulas, 1).Select

 

[EssKayKay]

You can't place nothing in a cell that has a formula in it (in spite of many requests for a BLANK() function like DAX has).

The best solution will depend on why there are 'blank' cells and what you are actually doing. It might be filtering, or looping. Selecting cells at all is almost never necessary though.

Thanks Rory. I'll try to do this a bit differently. I will create another post when I get this

@RajeshKumar1313 did you test this with the OP's scenario?

Book1
	A	B
1	0
2	0
3	dddd	100
4	0
5	0
6	1	100
7	2	100
8	3	100
9	4	100
10	5	100
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=IF(A1<=0,"",100)

Result I got is below (which is what I expected)

View attachment 115831

I think this is getting really close to what I had in mind. I'm going to look at this closer tomorrow. I have another issue I'm working on. I will get back to you and Rory. I thank you both for your time and suggestions. I'll keep you posted.

Again, so appreciated,
Steve K.

 

[EssKayKay]

@RajeshKumar1313 did you test this with the OP's scenario?

Book1
	A	B
1	0
2	0
3	dddd	100
4	0
5	0
6	1	100
7	2	100
8	3	100
9	4	100
10	5	100
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=IF(A1<=0,"",100)

Result I got is below (which is what I expected)

View attachment 115831

Hey Mark - I'm back,
I ran into a problem when attempting to add your code and my full worksheet. It worked great with my sample info which I originally include here.

However, my actual code is =IF(M33>0,"",C33) which is placed in column V. I assume the reason this is not working as expected is because the column numbers etc. don’t match your code (based on my sample example).

I apologize if this comes off as being a bit demanding here which I surely don’t mean to be, but could you review/modify your code to reflect my exact column and formulas?

Sorry for such a request but I am not a programmer -- but I try. I hope you understand

Thanks again,
Steve

 

[EssKayKay]

Mark,
I noticed some other problems, namely (like in my sample) there can be blank cells at the end. I only want the range to be cells with a 100. In my example below I would want the range to be B7:B10 even though there is data in rows 11&12.

For right now, let's just put this on hold. I've got another idea.
Thank you for your support and assistance. Sorry for being such a pest.

 

[MARK858]

I know you said that you have it on hold but having blank cells at the end shouldn't make a difference (even if they aren't blank but "")

With ""

Book1
	A	B
4	0
5	0
6	1	100
7	2	100
8	3	100
9	4	100
10	5	100
11	0
12	0
Sheet1

Cell Formulas
Range	Formula
B4:B12	B4	=IF(A4<=0,"",100)

I get

With blank cells

Book1
	A	B
4	0
5	0
6	1	100
7	2	100
8	3	100
9	4	100
10	5	100
11	0
12	0
Sheet1

Cell Formulas
Range	Formula
B4:B10	B4	=IF(A4<=0,"",100)

I get

As for

However, my actual code is =IF(M33>0,"",C33) which is placed in column V.

Then you would need to adjust the offset something like

    Dim myRng As Range

    Set myRng = Range("M33:M43").SpecialCells(xlCellTypeConstants, 1)
    myRng.Offset(, 9).SpecialCells(xlCellTypeFormulas, 1).Select

Which would give

Book1
	B	C	D									M	N								V
33		100										0									100
34												0									100
35												1
36												2
37												3
38												4
39												5
40												0									100
41												0									100
Sheet1

Cell Formulas
Range	Formula
V33:V41	V33	=IF(M33>0,"",$C$33)