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

 

[RoryA]

No, there is no NULL in Excel.

 

[EssKayKay]

A follow up if you please -

So what/how would you recommend placing "nothing" in a cell(s)?
What alternative could there be to do a Range(Selection, Selection.End(xlUp)).Select and only selecting cells that are >0 (or something like that) where there would be only "blank" cells in the upper most row. All the values would follow the blank cells (or ones with text)?

EDIT - I'm still interested in the question here but I may have to rethink this a bit. If you have any suggestions to this I'm still most certainly interested.
I'll be Back. . .

 

[RoryA]

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.

 

[RajeshKumar1313]

Try This
Sub Range_Selection()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Dim i As Integer
i = 2
For i = 2 To lastrow
If Range("b" & i).Value > 1 Then
Range("B" & i).Select
Exit For
Else
End If
Next i
Range(Selection, Selection.End(xlDown)).Select
end Sub

 

[MARK858]

@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)

 

[RajeshKumar1313]

@MARK858 i have created based on the attached snip.


for above scenario below code will work.
Sub RANGESET()

Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Dim i As Integer
i = 2
For i = 2 To lastrow
If IsNumeric(Range("b" & i).Value) And IsNumeric(Range("b" & i).Value > 1) Then
Range("B" & i).Select
Exit For
Else
End If
Next i
Range(Selection, Selection.End(xlDown)).Select
End Sub

 

[MARK858]

So how are you recommending placing Nothing in the cells (as per your snip) as the OP is using a formula?

Btw the i = 2 is doing nothing as the next line tells it to start at 2

i = 2
For i = 2 To lastrow

 

[RajeshKumar1313]

he mentioned
alternative could there be to do a Range(Selection, Selection.End(xlUp)).Select and only selecting cells that are >0 (or something like that)

 

[MARK858]

You can't just ignore that "" is treated as greater than zero