Object required when formatting

[Todd Huttenstine]

Sheets("WOW Reps").Range("B" & Counter) = Cell1.Offset(1,
2).Value
Format(Sheets("WOW Reps").Range("C" & Counter), "0.00%") =
Cell1.Offset(0, 3).Value
Sheets("WOW Reps").Range("D" & Counter) = Cell1.Offset(0,
2).Value

Why do I get the error object required on the second line
of code? I am trying to format it to % as 0.00%.

 

[Rob Bovey]

Hi Todd,

Try it like this:

Sheets("WOW Reps").Range("B" & Counter) = Cell1.Offset(1, 2).Value
With Sheets("WOW Reps").Range("C" & Counter)
.NumberFormat = "0.00%"
.Value = Cell1.Offset(0, 3).Value
End With
Sheets("WOW Reps").Range("D" & Counter) = Cell1.Offset(0, 2).Value

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Todd Huttenstine]

Actually I figured it out after I posted. I changed it
to...

Sheets("WOW Reps").Range("B" & Counter) = Cell1.Offset(1,
2).Value
Sheets("WOW Reps").Range("C" & Counter) = Format
(Cell1.Offset(0, 3).Value, "0.00%")
Sheets("WOW Reps").Range("D" & Counter) = Cell1.Offset(0,
2).Value

I had my format on the wrong part. What is the benfit of
using the with?

 

[Rob Bovey]

Hi Todd,

The With...End With construct saves execution time when you are
performing multiple operations on the same base object. Your revised method
of accomplishing it with one operation will work fine, I was using two to
illustrate the two implicit operations that were occurring: applying a
number format to a cell and adding a value to a cell.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *