If then Statement and numberformat

[ysherriff]

Hi all, I have a table that changes based on the text in the dropdown. The name of the dropdown is Trend Drop down and the name of the table is Yearly Trend Tbl. Two of the metrics in the table should be formatted as percent while the remaining selection should be formatted as number with comma. As you can see when I test the macro, it gives me an error highlighted in yellow. the error is type mismatch. This is my first foray into actually building formulas in vba and was wondering what am I doing wrong?

The file is located at this link below. The table is located on a sheet called Yearly Trend View and the macro is located in a module title "change_numformat"

https://www.dropbox.com/s/fk72zjyefskis21/03-2015 -GLC Trend Report.zip?dl=0

 

[Luke M]

Your Or statement is slightly off in syntax. Need to fully list out each comparison.

Sub NumberFormat()

'First, let's make it so I don't have to write out range everytime
Dim trRange As Range
Dim yrRange As Range

'Define the ranges
Set myRange = Range("TREND_DROP_DOWN")
Set yrRange = Range("YEARLY_TREND_TBL")

'Now, correct syntax for Or is:
If trRange.Value = "OCCUPANCY" Or trRange.Value = "REHOSPITALIZATION" Then
    yrRange.NumberFormat = "0%"
Else
    yrRange.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
End If

End Sub

 

[ysherriff]

Thanks Luke. I will try it. When I wrote the formula, it seem to me the syntax was off but did not know I had to declare and set the ranges.

 

[Luke M]

Apologies for confusion...you don't have to declare and set the ranges. I just did that because I'm a lazy programmer and I don't like having to re-type range names. You could do the same thing w/o using variables. It's only the syntax that was an issue.