CODEWITHSUNDEEP
excelvba
VBAnoob
VBAnoob

Reputation: 163

Excel VBA If statment range percentage

I have this code that makes "products2" either lowercase or uppercase based on the Integer "Aa". "Aa" is a cell that will either be 89% to 94.9999% in which case products should be lowercase, or it will be 95% to 100% in which case it will be uppercase (elseif). How do I format the If statement so that it does this? 

         If Aa = <89% and >94.9999% Then
             Products2 = Chr(J + Asc("a"))
               MsgBox "Products2 = " & Products2
          ElseIf Aa = <95% to >100% Then
             Products2 = Chr(J + Asc("A"))
                MsgBox "Products2 = " & Products2
          End If

Upvotes: 0

Views: 2067

Answers (1)

Don Jewett
Don Jewett

Reputation: 1967

You need to have each comparison joined with And

If Aa >= .89 And Aa < .95 Then
    'blah
ElseIf Aa >= .95 And Aa <= 1 Then
    'blah
End If

This is the logical equivalent of this (with parentheses)

If (Aa >= .89) And (Aa < .95) Then
    'blah
ElseIf (Aa >= .95) And (Aa <= 1) Then
    'blah
End If

But I would actually write this as a Select statement because it supports ranges, which is more readable.

Select Case Aa
    Case .95 To 1
        'blah
    Case .89 To .95
        'blah
End Select

Notice I have reversed the order. This is to use .95 for a comparison in the second case, rather than .949999, which if you're not careful could miss floating point values between .949999 and .95

This does leave out other cases, though. If your logic is really that anything above 95% is one way and everything below 95% is another, then this is even better:

Select Case Aa
    Case >= .95
        'blah
    Case Else
        'blah
End Select

which is now the equivalent of:

If Aa >= .95 Then
    'blah
Else
    'blah
End If

In my opinion one of these last two is best, because it covers all cases. If you don't, you could end up with an edge case which does not change your formatting, which could be misleading, because the formatting may not reflect the current state.

Upvotes: 4

Related Questions