Name range down one cell

[tpeter]

I am building an if statement to check and see if the cell under Plt_1001
name range is blank. How do you refer to this? Thank you for your help again.


Private Sub UserForm_Activate()
Range("Plt_1001").Select
If Range("Plt_1001") -1.value = "" Then ' Doesn't work
Range("Plt_1001").Select
Else
Call Plant_1001
End If
End Sub

 

[Jacob Skaria]

Try the below

If Range("Plt_1001").Offset(1) = "" Then
MsgBox "cell blank"
End If

If this post helps click Yes

 

[tpeter]

Jacob,

Thank you for your help. When I attempt to run it, it will still not work
(getting error). I am attempting to look at the cell below range "Plt_1001"
and if it is blank then bring up a user form to insert rows, if it is not
blank delete all but one row ("Plt_1001"). Everything works except for my
ability to refer to the cell below the named range ( c:w). I hope this
clarifies a little better than before. I have to refer to the name range
because there may be 1000 rows above it or 5 this is a variable.

 

[Jacob Skaria]

What's the error.If it is 1004 then check whether the name is correct.
Just try the below test

--Open a fresh workbook. Select cell C10. name it as 'myRange'
--Try the below code. and then fill C11 with data and try the below code ..

Sub Macro()
If Trim(Range("myRange").Offset(1)) = "" Then MsgBox "cell blank"
End Sub

If this post helps click Yes

 

[Dave Peterson]

Since PLT_1001 is multiple cells, you can't compare the .value with a single
value.

But you could do:

with worksheets("somesheetnamehere")
if application.counta(.range("plt_1001").offset(1,0)) = 0 then
'the row under the single row, but multiple columns is empty
else
'there's something in at least one of the cells
end if
end with

If that PLT_1001 range is multiple rows, multiple columns and/or multiple areas,
you'll have to give more information on what should be checked...

 

[tpeter]

Jacob I tryed the code below and it works you are correct. I think what my
problem is, is that Range("Plt_1001") is a range of cells from column c:w and
I am trying to only look at the cell under the named range in column "c"
only. I am sorry I haven't been more clear. Hope this helps on why this will
not work.

 

[Jacob Skaria]

One way ...

If Cells(Range("Plt_1001").Row + Range("Plt_1001").Rows.Count, _
Range("Plt_1001").Column) = "" Then MsgBox "cell blank"

If this post helps click Yes

 

[tpeter]

Jacob,

It is still tweaking out, here is the other macros so you can see it:

Userform:
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Range("z3").Value = TextBox6.Value
Call Plant_1001
End Sub

Macro that inserts cells:

Sub Plant_1001()
Range("Plt_1001").Select
Selection.Copy
Range("Plt_1001").Resize(Range("z3") - 1).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub
Macro to delete cells
Sub Plt_1001_remove()

Range("plt_1001").Offset(-Range("z3") + 1).Resize(Range("z3") - 1).Delete
Shift:=xlUp
End Sub


Macro to evalute if the cell under Plt_1001 is blank, if it is then bring up
the user form if it isn't then delete all rows so I only have Plt_1001 (start
point)
Private Sub UserForm_Activate()
If Cells(Range("Plt_1001").Row + Range("Plt_1001")row.Count,
Range("Plt_1001").Column) = "" Then
Range("Plt_1001").Select
Else
Call Plant_1001
End If

End Sub

 

[tpeter]

I have found the issue, I am not evaluating the correct thing, sorry I have
been staring at this to long. Currently range("Plt_1001") is c15:w15 (this
row can change) I will always have a blank cell underneath this range no
matter what. From a baseline of one row (Plt_1001) the constant is that there
is 2 rows above it with data, then a blank cell seperating different area's.
The criteria show look up 3 rows from Plt_1001 and if there is data there
call "Plt_1001_remove". if there is only 2 rows above it then bring up the
userform and insert additional rows.
Sorry for the confusion.

 

[tpeter]

Works!!!!!!!!!!!!!!

Private Sub UserForm_Activate()

With Worksheets("Matl Form")
If Application.CountA(.Range("plt_1001").Offset(-3, 0)) = 0 Then
Range("Plt_1001").Select
Else
Call Plt_1001_remove
End If
End With
End Sub