Number Format

[tquist]

I can't seem to get a cell formated the way I want. I would like to format ActiveCell.Offset(2, 0) as a percentage. I keep getting an error saying "unable to set the NumberFormat property of the Range class. Any suggestions? The problem area is at the end of the code...

Sub TablePopulator()
'
' Statistics and Chart Source Table Generator Macro by Tom Quist
'
Dim Jurisdictions As Range
Dim Cartersville As Range
Dim Responses As Range
Set Responses = Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(17, 0))
Set Cartersville = Application.InputBox(prompt:="Select the Cartersville Value", Title:="Cartersville", Type:=8)
Set Jurisdictions = Application.InputBox(prompt:="Select the Jurisdictions Range", Title:="Jurisdictions", Type:=8)
ActiveCell.Offset(1, 0) = "=if(isnumber(" & Cartersville.Address(external:=True) & "), (" & Cartersville.Address(external:=True) & "), NA())"
ActiveCell.Offset(2, 0) = "=if(isnumber(" & Cartersville.Address(external:=True) & "), Percentrank(" & Jurisdictions.Address(external:=True) & "," & Cartersville.Address(external:=True) & "),"""")"
ActiveCell.Offset(3, 0) = "=Median(" & Jurisdictions.Address(external:=True) & ")"
ActiveCell.Offset(4, 0) = "=count(" & Jurisdictions.Address(external:=True) & ")"
ActiveCell.Offset(5, 0) = "=Max(" & Jurisdictions.Address(external:=True) & ")"
ActiveCell.Offset(6, 0) = "=Min(" & Jurisdictions.Address(external:=True) & ")"
ActiveCell.Offset(7, 0) = "=Percentile(" & Jurisdictions.Address(external:=True) & ",.1)"
ActiveCell.Offset(8, 0) = "=Percentile(" & Jurisdictions.Address(external:=True) & ",.25)"
ActiveCell.Offset(9, 0) = "=Percentile(" & Jurisdictions.Address(external:=True) & ",.75)"
ActiveCell.Offset(10, 0) = "=Percentile(" & Jurisdictions.Address(external:=True) & ",.9)"
ActiveCell.Offset(11, 0) = "=(" & ActiveCell.Offset(8, 0).Address(external:=True) & ")"
ActiveCell.Offset(12, 0) = "=(" & ActiveCell.Offset(3, 0).Address(external:=True) & ") - (" & ActiveCell.Offset(8, 0).Address(external:=True) & ")"
ActiveCell.Offset(13, 0) = "=(" & ActiveCell.Offset(9, 0).Address(external:=True) & ") - (" & ActiveCell.Offset(3, 0).Address(external:=True) & ")"
ActiveCell.Offset(14, 0) = "=(" & ActiveCell.Offset(8, 0).Address(external:=True) & ") - (" & ActiveCell.Offset(6, 0).Address(external:=True) & ")"
ActiveCell.Offset(15, 0) = "=(" & ActiveCell.Offset(5, 0).Address(external:=True) & ") - (" & ActiveCell.Offset(9, 0).Address(external:=True) & ")"
ActiveCell.Offset(16, 0) = "=(" & ActiveCell.Offset(3, 0).Address(external:=True) & ") - (" & ActiveCell.Offset(7, 0).Address(external:=True) & ")"
ActiveCell.Offset(17, 0) = "=(" & ActiveCell.Offset(10, 0).Address(external:=True) & ") - (" & ActiveCell.Offset(3, 0).Address(external:=True) & ")"
Responses.Font.Size = 8
ActiveCell.Offset(2, 0).NumberFormat = "Percent"
'
End Sub

 

[Richard Schollar]

Hi

Try applying the format like so:

ActiveCell.Offset(2, 0).NumberFormat = "0.0%;-0.0%;0%"

 

[tquist]

Thanks! That worked fabulously. I appreciate your help!

 

[tquist]

Whoops! I spoke to soon (not on the thank you part).

The code has formated not only ActiveCell.Offset(2, 0) as a percentage, but also ActiveCell.Offset(1, 0). Any suggestions on how to prevent this?

Thank you!

Tom

 

[tquist]

Nevermind. I'm an idiot. Scratch that last comment. It DOES work perfectly. You rock.

Tom