Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error
Greetings,
When I run the macro below, everything works fine until I get to the . NumberFormat line. Then I get an error message that says Object required. What I am trying to do is remove any colors and erase the cells contents in the range, and then set the format to numeric with commas and no zeros. How can I make this work? Thanks in advance for your help! Ray Sub ResetSheet() Dim bclro As Long Application.ScreenUpdating = False ' turn off screen updating bclro = Cells(65536, 1).End(xlUp).row With Range(Cells(30, 1), Cells(bclro, 55)) .Interior.ColorIndex = xlNone .Delete .NumberFormat = "#,##0" End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error
Ray,
In your code, you've Deleted the cell in question, so then the With statement is pointing at Nothing when you try to use the NumberFormat property. Rather than use Delete, you probably want to use Clear. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ray Batig" wrote in message nk.net... Greetings, When I run the macro below, everything works fine until I get to the . NumberFormat line. Then I get an error message that says Object required. What I am trying to do is remove any colors and erase the cells contents in the range, and then set the format to numeric with commas and no zeros. How can I make this work? Thanks in advance for your help! Ray Sub ResetSheet() Dim bclro As Long Application.ScreenUpdating = False ' turn off screen updating bclro = Cells(65536, 1).End(xlUp).row With Range(Cells(30, 1), Cells(bclro, 55)) .Interior.ColorIndex = xlNone .Delete .NumberFormat = "#,##0" End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error
Thanks Chip,
I used the recorder to set up the macro and pushed the delete key. Now I know better.... Ray Chip Pearson wrote in message ... Ray, In your code, you've Deleted the cell in question, so then the With statement is pointing at Nothing when you try to use the NumberFormat property. Rather than use Delete, you probably want to use Clear. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ray Batig" wrote in message nk.net... Greetings, When I run the macro below, everything works fine until I get to the . NumberFormat line. Then I get an error message that says Object required. What I am trying to do is remove any colors and erase the cells contents in the range, and then set the format to numeric with commas and no zeros. How can I make this work? Thanks in advance for your help! Ray Sub ResetSheet() Dim bclro As Long Application.ScreenUpdating = False ' turn off screen updating bclro = Cells(65536, 1).End(xlUp).row With Range(Cells(30, 1), Cells(bclro, 55)) .Interior.ColorIndex = xlNone .Delete .NumberFormat = "#,##0" End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Error
The macro recorder should emit a ClearContents command rather
than Delete. At least it does for me. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ray Batig" wrote in message ink.net... Thanks Chip, I used the recorder to set up the macro and pushed the delete key. Now I know better.... Ray Chip Pearson wrote in message ... Ray, In your code, you've Deleted the cell in question, so then the With statement is pointing at Nothing when you try to use the NumberFormat property. Rather than use Delete, you probably want to use Clear. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ray Batig" wrote in message nk.net... Greetings, When I run the macro below, everything works fine until I get to the . NumberFormat line. Then I get an error message that says Object required. What I am trying to do is remove any colors and erase the cells contents in the range, and then set the format to numeric with commas and no zeros. How can I make this work? Thanks in advance for your help! Ray Sub ResetSheet() Dim bclro As Long Application.ScreenUpdating = False ' turn off screen updating bclro = Cells(65536, 1).End(xlUp).row With Range(Cells(30, 1), Cells(bclro, 55)) .Interior.ColorIndex = xlNone .Delete .NumberFormat = "#,##0" End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
Runtime Error 1004 -- Application Defined or Object Defined Error | Excel Programming |