Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Null Value to Range Property
I am setting a range ojbect to a specific range:
Set Rng1 = ActualUsedRange ActualUsedRange is a public function which returns a range by selected certain cells in a sheet. Now I know that when I am in a blank worksheet the VBA UsedRange property as displayed in the Immediate Window comes up empty" ?Worksheets("Sheet1").UsedRange There are instances where I will not have a range to pass to Rng1 above via my ActualUsedRange function. For example in a blank worskeet. What error handling do need to put in so that the following line of code will not fail. The way I am currently doing it, I get a Runtime Error 424 Object Required. For Each Cell In Rng1 Thanks EM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Null Value to Range Property
Hi. here's one way:
If Not Rng1 Is Nothing Then For Each Cell In Rng1 Next End If -- Hope that helps. Vergel Adriano "ExcelMonkey" wrote: I am setting a range ojbect to a specific range: Set Rng1 = ActualUsedRange ActualUsedRange is a public function which returns a range by selected certain cells in a sheet. Now I know that when I am in a blank worksheet the VBA UsedRange property as displayed in the Immediate Window comes up empty" ?Worksheets("Sheet1").UsedRange There are instances where I will not have a range to pass to Rng1 above via my ActualUsedRange function. For example in a blank worskeet. What error handling do need to put in so that the following line of code will not fail. The way I am currently doing it, I get a Runtime Error 424 Object Required. For Each Cell In Rng1 Thanks EM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Null Value to Range Property
Isn't UsedRange always at least one cell, even in an empty worksheet.
Anyways, how about If Not Rng1 Is Nothing Ten Foe Each Cell In Rng1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ExcelMonkey" wrote in message ... I am setting a range ojbect to a specific range: Set Rng1 = ActualUsedRange ActualUsedRange is a public function which returns a range by selected certain cells in a sheet. Now I know that when I am in a blank worksheet the VBA UsedRange property as displayed in the Immediate Window comes up empty" ?Worksheets("Sheet1").UsedRange There are instances where I will not have a range to pass to Rng1 above via my ActualUsedRange function. For example in a blank worskeet. What error handling do need to put in so that the following line of code will not fail. The way I am currently doing it, I get a Runtime Error 424 Object Required. For Each Cell In Rng1 Thanks EM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Null Value to Range Property
?Worksheets("Sheet1").UsedRange
is the same as: ?Worksheets("Sheet1").UsedRange.Value If the sheet is empty, the .usedrange.address will be $A$1. And printing the value of that single cell will show an empty string. (If there is more than one cell in the usedrange, then that line will fail.) You may want to use: set rng1 = actualusedrange if rng1.address = "$A$1" then if isempty(rng1.value) then msgbox "the sheet is empty" end if end if ExcelMonkey wrote: I am setting a range ojbect to a specific range: Set Rng1 = ActualUsedRange ActualUsedRange is a public function which returns a range by selected certain cells in a sheet. Now I know that when I am in a blank worksheet the VBA UsedRange property as displayed in the Immediate Window comes up empty" ?Worksheets("Sheet1").UsedRange There are instances where I will not have a range to pass to Rng1 above via my ActualUsedRange function. For example in a blank worskeet. What error handling do need to put in so that the following line of code will not fail. The way I am currently doing it, I get a Runtime Error 424 Object Required. For Each Cell In Rng1 Thanks EM -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing several parameters to OnAction property. | Excel Discussion (Misc queries) | |||
Get Property value of class instance by passing string property name | Excel Programming | |||
Property Let: assign return value of Double when passing String | Excel Discussion (Misc queries) | |||
Some worksheet returns NULL for CodeName property. Why? | Excel Programming | |||
Passing null to a function | Excel Programming |