Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
Try the below
If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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" wrote: Try the below If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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 --------------- Jacob Skaria "tpeter" wrote: 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" wrote: Try the below If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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 wrote: 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" wrote: Try the below If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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" wrote: 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 --------------- Jacob Skaria "tpeter" wrote: 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" wrote: Try the below If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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 --------------- Jacob Skaria "tpeter" wrote: 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" wrote: 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 --------------- Jacob Skaria "tpeter" wrote: 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" wrote: Try the below If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "tpeter" wrote: 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" wrote: 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 --------------- Jacob Skaria "tpeter" wrote: 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" wrote: Try the below If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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. "Dave Peterson" wrote: 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 wrote: 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" wrote: Try the below If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 -- Dave Peterson . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name range down one cell
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 "Dave Peterson" wrote: 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 wrote: 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" wrote: Try the below If Range("Plt_1001").Offset(1) = "" Then MsgBox "cell blank" End If If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: 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 -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP W/ VBA: SELECT RANGE, ALLCAPS, CELL COLOR, RETURN TO BLANK CELL/PATTERN CELL | Excel Programming | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Change cell colour for a cell or range within a predefined range | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming |