Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
Hello...I have a little vb code tht I am trying to get to work with a range
of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
Assuming the cells in the range contain data and not formulas, give this
coding idea a try... On Error Resume Next If Sheets("Sr-Area Manager").Range("D14:D63"). _ SpecialCells(xlCellTypeBlanks).Count Then ' ' There is at least one empty cell in ' the range of data so run your code here ' End If On Error GoTo 0 -- Rick (MVP - Excel) "Randy" wrote in message ... Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
Sub BlankDetector()
If Application.WorksheetFunction.CountBlank(Range("D1 4:D63")) 0 Then MsgBox "data misssing" End If End Sub -- Gary''s Student - gsnu200909 "Randy" wrote: Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
I'd try:
Private Sub Sr_Mgr_Click() with workSheets("Sr-Area Manager").Range("D14:D63") if .cells.count = application.counta(.cells) then 'all the cells have something in them else 'not all the cells have something in them end if end with End Sub Remember that formulas that evaluate to ="" (looking empty) still count as having something in them. Randy wrote: Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
Awesome Gary! A little tweaking and worked like a charm. Thank you very much!
Now I have another question for ya....rows 14-38 are visible....rows 39-83 are hidden unless the user clicks a botton that makes rows 39-83 visible...is there a way I cn get this to work on rows 39-83 when they are visible only? -- Randy Street Rancho Cucamonga, CA "Gary''s Student" wrote: Sub BlankDetector() If Application.WorksheetFunction.CountBlank(Range("D1 4:D63")) 0 Then MsgBox "data misssing" End If End Sub -- Gary''s Student - gsnu200909 "Randy" wrote: Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
Thanks Dave....you are awesome as always! Now I have another question for
you....rows 14-38 are visible....rows 39-83 are hidden unless the user clicks a botton that makes rows 39-83 visible...is there a way I can get this to work on rows 39-83 when they are visible only? -- Randy Street Rancho Cucamonga, CA "Dave Peterson" wrote: I'd try: Private Sub Sr_Mgr_Click() with workSheets("Sr-Area Manager").Range("D14:D63") if .cells.count = application.counta(.cells) then 'all the cells have something in them else 'not all the cells have something in them end if end with End Sub Remember that formulas that evaluate to ="" (looking empty) still count as having something in them. Randy wrote: Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA -- Dave Peterson . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
Thank you Rick! Now I have another question for ya....rows 14-38 are
visible....rows 39-83 are hidden unless the user clicks a botton that makes rows 39-83 visible...is there a way I can get this to work on rows 39-83 when they are visible only? -- Randy Street Rancho Cucamonga, CA "Rick Rothstein" wrote: Assuming the cells in the range contain data and not formulas, give this coding idea a try... On Error Resume Next If Sheets("Sr-Area Manager").Range("D14:D63"). _ SpecialCells(xlCellTypeBlanks).Count Then ' ' There is at least one empty cell in ' the range of data so run your code here ' End If On Error GoTo 0 -- Rick (MVP - Excel) "Randy" wrote in message ... Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
Option Explicit
Private Sub Sr_Mgr_Click() Dim myRng As Range Set myRng = Nothing On Error Resume Next Set myRng = Worksheets("Sr-Area Manager").Range("D14:D63") _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If myRng Is Nothing Then 'all hidden--everything is ok (right?) Else With myRng If .Cells.Count = Application.CountA(.Cells) Then MsgBox "all the cells have something in them" Else MsgBox "not all the cells have something in them" End If End With End If End Sub Randy wrote: Thanks Dave....you are awesome as always! Now I have another question for you....rows 14-38 are visible....rows 39-83 are hidden unless the user clicks a botton that makes rows 39-83 visible...is there a way I can get this to work on rows 39-83 when they are visible only? -- Randy Street Rancho Cucamonga, CA "Dave Peterson" wrote: I'd try: Private Sub Sr_Mgr_Click() with workSheets("Sr-Area Manager").Range("D14:D63") if .cells.count = application.counta(.cells) then 'all the cells have something in them else 'not all the cells have something in them end if end with End Sub Remember that formulas that evaluate to ="" (looking empty) still count as having something in them. Randy wrote: Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA -- Dave Peterson . -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
You my friend are simply amazing! I cannot wait till I get to your level!
Thanks gain and have a wonderful holiday season! -- Randy Street Rancho Cucamonga, CA "Dave Peterson" wrote: Option Explicit Private Sub Sr_Mgr_Click() Dim myRng As Range Set myRng = Nothing On Error Resume Next Set myRng = Worksheets("Sr-Area Manager").Range("D14:D63") _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If myRng Is Nothing Then 'all hidden--everything is ok (right?) Else With myRng If .Cells.Count = Application.CountA(.Cells) Then MsgBox "all the cells have something in them" Else MsgBox "not all the cells have something in them" End If End With End If End Sub Randy wrote: Thanks Dave....you are awesome as always! Now I have another question for you....rows 14-38 are visible....rows 39-83 are hidden unless the user clicks a botton that makes rows 39-83 visible...is there a way I can get this to work on rows 39-83 when they are visible only? -- Randy Street Rancho Cucamonga, CA "Dave Peterson" wrote: I'd try: Private Sub Sr_Mgr_Click() with workSheets("Sr-Area Manager").Range("D14:D63") if .cells.count = application.counta(.cells) then 'all the cells have something in them else 'not all the cells have something in them end if end with End Sub Remember that formulas that evaluate to ="" (looking empty) still count as having something in them. Randy wrote: Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA -- Dave Peterson . -- Dave Peterson . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
IsNotEmpty Range
This should do it for you...
Dim L As Long ...... ...... On Error Resume Next L = Sheets("Sr-Area Manager").Range("A1:E9").SpecialCells( _ xlCellTypeVisible).SpecialCells(xlCellTypeBlanks). Count On Error GoTo 0 If L 0 Then ' ' There is at least one empty cell in ' the range of data so run your code here ' End If -- Rick (MVP - Excel) "Randy" wrote in message ... Thank you Rick! Now I have another question for ya....rows 14-38 are visible....rows 39-83 are hidden unless the user clicks a botton that makes rows 39-83 visible...is there a way I can get this to work on rows 39-83 when they are visible only? -- Randy Street Rancho Cucamonga, CA "Rick Rothstein" wrote: Assuming the cells in the range contain data and not formulas, give this coding idea a try... On Error Resume Next If Sheets("Sr-Area Manager").Range("D14:D63"). _ SpecialCells(xlCellTypeBlanks).Count Then ' ' There is at least one empty cell in ' the range of data so run your code here ' End If On Error GoTo 0 -- Rick (MVP - Excel) "Randy" wrote in message ... Hello...I have a little vb code tht I am trying to get to work with a range of cells. Basically if any of the cells in the range are empty it should return a message. the code works if a single cell (such as D14) is used but not when I put the range (such as D14:D63). Anyone have any ideas as to how I can get this to work without listing each cell individually? here is what I hve so far: Private Sub Sr_Mgr_Click() If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then r = MsgBox("You are missing a Move-In Date. Move-In Dates are a required field. Please verify you have ALL Move-In Dates entered before continuing!", _ vbQuestion + vbOKOnly, "Error?") If r = vbOK Then Range("D14").Select End End If End If End Sub -- Randy Street Rancho Cucamonga, CA . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |