Solved: Why is this line failing on me?

[Saladsamurai]

I have used the NumberFormat method plenty of times before. The Variable NumScenario is defined and takes on a value before executing this line:

Sheet2.Range(Cells(NumScenario + 5, 3), Cells(NumScenario + 7, 3)).NumberFormat = "0%"

[Saladsamurai]

Oh Excel! Why do I even bother? Just on a whim I tried Activating sheet2 first and it worked just fine.

Why the heck do I have to activate the sheet for the .NumberFormat but I do not have to activate it for the .Cells.Interior.ColorIndex ?

[Bob Phillips]

You do not need to activate Sheet2. The problem is that you qualify the Range object with Sheet2, but not the Cells object(s).

Use



With Sheet2

.Range(.Cells(NumScenario + 5, 3), .Cells(NumScenario + 7, 3)).NumberFormat = "0%"
End With

[Saladsamurai]

You do not need to activate Sheet2. The problem is that you qualify the Range object with Sheet2, but not the Cells object(s).

Use



With Sheet2

.Range(.Cells(NumScenario + 5, 3), .Cells(NumScenario + 7, 3)).NumberFormat = "0%"
End With


I see. I guess I could also use the annoyingly long

Sheet2.Range(Sheet2.Cells(NumScenario + 5, 3),Sheet2.Cells(NumScenario + 7, 3)).NumberFormat = "0%"


?

I guess that I thought it should have been implicit that I meant Sheet2. Oh Excel, why can't you read my mind?

[Bob Phillips]

You could, but that means VBA will do three 'gets' of the sheet object, every time it is referenced. By doing a With, it just does it the once.

[Saladsamurai]

You could, but that means VBA will do three 'gets' of the sheet object, every time it is referenced. By doing a With, it just does it the once.

Splendid! I like these little tidbits :)

[mdmackillop]

BTW, you don't need to qualify the range

With Sheet2
Range(.Cells(NumScenario + 5, 3), .Cells(NumScenario + 7, 3)).NumberFormat = "0%"
End With

[Bob Phillips]

You know MD, I always used to think that, after all it is a range you are referencing, not THE range in sheet2, but I have had problems, so I always do it now.