Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
Hi All
I'm using a bit of code to copy formulae into blank cells using 'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an error on the occassions when there are no blank cells. I've tried using an On Error statement but doesn't seem to work. Any ideas how I get around the error msg? Code below Regards Trevor Williams Range("A1").CurrentRegion.Select On Error GoTo CleanUp Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" also tried: If Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks).Select = True Then Selection.FormulaR1C1 = "=R[-1]C" End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
Your first method looks OK assuming code also includes an error handler, but
difficult to say based on the little you posted. No need to select of course, try something like this Sub test() Dim rng As Range On Error GoTo errH Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R[-1]C" End If CleanUp: ' cleanup code Exit Sub errH: Resume CleanUp End Sub or On Error resume next ' anticipated error Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks) On Error GoTo errH ' resume normal error handling if not rng is nothing then Regards, Peter T "Trevor Williams" wrote in message ... Hi All I'm using a bit of code to copy formulae into blank cells using 'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an error on the occassions when there are no blank cells. I've tried using an On Error statement but doesn't seem to work. Any ideas how I get around the error msg? Code below Regards Trevor Williams Range("A1").CurrentRegion.Select On Error GoTo CleanUp Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" also tried: If Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks).Select = True Then Selection.FormulaR1C1 = "=R[-1]C" End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
Hi Peter - thanks for the response.
My procedure called CleanUp is the error handler, and basically protects the activesheet and exits the sub - Is there a reason why the On Error line can't go directly to CleanUp? I'll make the amends as you've suggested... Thanks again Trevor "Peter T" wrote: Your first method looks OK assuming code also includes an error handler, but difficult to say based on the little you posted. No need to select of course, try something like this Sub test() Dim rng As Range On Error GoTo errH Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R[-1]C" End If CleanUp: ' cleanup code Exit Sub errH: Resume CleanUp End Sub or On Error resume next ' anticipated error Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks) On Error GoTo errH ' resume normal error handling if not rng is nothing then Regards, Peter T "Trevor Williams" wrote in message ... Hi All I'm using a bit of code to copy formulae into blank cells using 'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an error on the occassions when there are no blank cells. I've tried using an On Error statement but doesn't seem to work. Any ideas how I get around the error msg? Code below Regards Trevor Williams Range("A1").CurrentRegion.Select On Error GoTo CleanUp Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" also tried: If Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks).Select = True Then Selection.FormulaR1C1 = "=R[-1]C" End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
Is there a reason why the On Error line can't go directly to CleanUp?
Should be fine also, like I said your first method looked OK. Regards, Peter T "Trevor Williams" wrote in message ... Hi Peter - thanks for the response. My procedure called CleanUp is the error handler, and basically protects the activesheet and exits the sub - Is there a reason why the On Error line can't go directly to CleanUp? I'll make the amends as you've suggested... Thanks again Trevor "Peter T" wrote: Your first method looks OK assuming code also includes an error handler, but difficult to say based on the little you posted. No need to select of course, try something like this Sub test() Dim rng As Range On Error GoTo errH Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R[-1]C" End If CleanUp: ' cleanup code Exit Sub errH: Resume CleanUp End Sub or On Error resume next ' anticipated error Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks) On Error GoTo errH ' resume normal error handling if not rng is nothing then Regards, Peter T "Trevor Williams" wrote in message ... Hi All I'm using a bit of code to copy formulae into blank cells using 'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an error on the occassions when there are no blank cells. I've tried using an On Error statement but doesn't seem to work. Any ideas how I get around the error msg? Code below Regards Trevor Williams Range("A1").CurrentRegion.Select On Error GoTo CleanUp Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" also tried: If Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks).Select = True Then Selection.FormulaR1C1 = "=R[-1]C" End If |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
OK, thanks again. I'll have a play.
"Peter T" wrote: Is there a reason why the On Error line can't go directly to CleanUp? Should be fine also, like I said your first method looked OK. Regards, Peter T "Trevor Williams" wrote in message ... Hi Peter - thanks for the response. My procedure called CleanUp is the error handler, and basically protects the activesheet and exits the sub - Is there a reason why the On Error line can't go directly to CleanUp? I'll make the amends as you've suggested... Thanks again Trevor "Peter T" wrote: Your first method looks OK assuming code also includes an error handler, but difficult to say based on the little you posted. No need to select of course, try something like this Sub test() Dim rng As Range On Error GoTo errH Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks) If Not rng Is Nothing Then rng.FormulaR1C1 = "=R[-1]C" End If CleanUp: ' cleanup code Exit Sub errH: Resume CleanUp End Sub or On Error resume next ' anticipated error Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks) On Error GoTo errH ' resume normal error handling if not rng is nothing then Regards, Peter T "Trevor Williams" wrote in message ... Hi All I'm using a bit of code to copy formulae into blank cells using 'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an error on the occassions when there are no blank cells. I've tried using an On Error statement but doesn't seem to work. Any ideas how I get around the error msg? Code below Regards Trevor Williams Range("A1").CurrentRegion.Select On Error GoTo CleanUp Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" also tried: If Range("A1").CurrentRegion.SpecialCells(xlCellTypeB lanks).Select = True Then Selection.FormulaR1C1 = "=R[-1]C" End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work around to SpecialCells(xlCellTypeBlanks)... | Excel Discussion (Misc queries) | |||
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete | Excel Programming | |||
Check SpecialCells(xlCellTypeBlanks) for 0 blanks | Excel Programming | |||
Help to hide rows, SpecialCells(xlCellTypeBlanks) doesn't work... | Excel Programming | |||
specialcells(xlcelltypeblanks) | Excel Programming |