Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check for IsEmpty(MyArray) = True
I want to check to see if an array is empty or not. The array is a named
range on the worksheet and if there are no entries in that range I want to GoTo another section of code. I am looping through several range names. What do I need to do? Option Explicit Dim myArray As Variant Dim card As Integer Dim port As Integer Dim arName As String Dim cntr As Integer Dim oldStatusBar As Boolean Dim newHour As Date Dim newMinute As Date Dim newSecond As Date Dim waitTime As Date Sub Run_All() card = 1 port = 0 cntr = 3 Application.ScreenUpdating = False For card = 1 To 13 For port = 0 To 11 If card = 7 Then card = card + 1 oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Calculating port " & card & "_" & port arName = "port" & card & "_" & port myArray = ThisWorkbook.Names(arName).RefersToRange.Value If IsEmpty(myArray) = True Then GoTo 1 'This line never goes to 1: Worksheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = card & "-" & port GetPort If Range("A13").Value = ("No Modem Data for this report") Then Range("A13").Cut Destination:=Range("A12") Filter Sort 1: cntr = cntr + 1 Next Next Sheet125.Select Range("I18").Formula = "=COUNTA('1-0:13-11'!A13:A100)" Application.StatusBar = "DONE!" newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 5 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar Application.ScreenUpdating = True End Sub Mike F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
check for IsEmpty(MyArray) = True
if Application.CountA(ThisWorkbook.Names(arName).Refe rsToRange) = 0 then
GoTo 1 -- Regards, Tom Ogilvy "Mike" wrote in message ... I want to check to see if an array is empty or not. The array is a named range on the worksheet and if there are no entries in that range I want to GoTo another section of code. I am looping through several range names. What do I need to do? Option Explicit Dim myArray As Variant Dim card As Integer Dim port As Integer Dim arName As String Dim cntr As Integer Dim oldStatusBar As Boolean Dim newHour As Date Dim newMinute As Date Dim newSecond As Date Dim waitTime As Date Sub Run_All() card = 1 port = 0 cntr = 3 Application.ScreenUpdating = False For card = 1 To 13 For port = 0 To 11 If card = 7 Then card = card + 1 oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Calculating port " & card & "_" & port arName = "port" & card & "_" & port myArray = ThisWorkbook.Names(arName).RefersToRange.Value If IsEmpty(myArray) = True Then GoTo 1 'This line never goes to 1: Worksheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = card & "-" & port GetPort If Range("A13").Value = ("No Modem Data for this report") Then Range("A13").Cut Destination:=Range("A12") Filter Sort 1: cntr = cntr + 1 Next Next Sheet125.Select Range("I18").Formula = "=COUNTA('1-0:13-11'!A13:A100)" Application.StatusBar = "DONE!" newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 5 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar Application.ScreenUpdating = True End Sub Mike F |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
check for IsEmpty(MyArray) = True
Thanks Tom,
That works great. It seems once declared, the array is not empty, even though there is no data in it. So I must count the items in the named range itself. Thank God it's Friday. Tom Ogilvy wrote in message ... if Application.CountA(ThisWorkbook.Names(arName).Refe rsToRange) = 0 then GoTo 1 -- Regards, Tom Ogilvy "Mike" wrote in message ... I want to check to see if an array is empty or not. The array is a named range on the worksheet and if there are no entries in that range I want to GoTo another section of code. I am looping through several range names. What do I need to do? Option Explicit Dim myArray As Variant Dim card As Integer Dim port As Integer Dim arName As String Dim cntr As Integer Dim oldStatusBar As Boolean Dim newHour As Date Dim newMinute As Date Dim newSecond As Date Dim waitTime As Date Sub Run_All() card = 1 port = 0 cntr = 3 Application.ScreenUpdating = False For card = 1 To 13 For port = 0 To 11 If card = 7 Then card = card + 1 oldStatusBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.StatusBar = "Calculating port " & card & "_" & port arName = "port" & card & "_" & port myArray = ThisWorkbook.Names(arName).RefersToRange.Value If IsEmpty(myArray) = True Then GoTo 1 'This line never goes to 1: Worksheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = card & "-" & port GetPort If Range("A13").Value = ("No Modem Data for this report") Then Range("A13").Cut Destination:=Range("A12") Filter Sort 1: cntr = cntr + 1 Next Next Sheet125.Select Range("I18").Formula = "=COUNTA('1-0:13-11'!A13:A100)" Application.StatusBar = "DONE!" newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 5 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Application.StatusBar = False Application.DisplayStatusBar = oldStatusBar Application.ScreenUpdating = True End Sub Mike F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
How do I make a checked check box = 1 instead of True? | Excel Worksheet Functions | |||
Continually check if condition is true in VBA | Excel Discussion (Misc queries) | |||
check box - true/false/na | Excel Discussion (Misc queries) | |||
Dim MyArray() as Long, Dim MyArray() as String, etc. | Excel Programming |