'Like' in VBA

[Nomis_Eswod]

Hello,

I am doing some work in VBA and I have one last bit of coding to do that I can't work out.

Basically, on my Excel sheets there are bits of information called 'Keys'. The key codes are:
10
12
50
51
94
96
99

There are 2 places on the spreadsheet that use these keys, lets says Key1 is the first section and Key2 is the second. I have an If formula in the VBA to say that if Key1 = Key 2 then do the following. I need to amend this slightly though. What I need it to say is that If they are part of the same key group then do the following. Grouping is simple, there are 3 groups, 10 & 12, 50 & 51 and 94 & 96 & 99.

So for example,

if Key1 = 10 or 12 and Key2 = 10 or 12 Then
Do the following
End If

But I don't want to have to write out every possible combination.

Any ideas??

 

[al_b_cnu]

Hi,

How about something like:

If Key1 <= 12 And Key2 <= 12 Then
    MsgBox "Group1"
ElseIf Key1 <= 51 And Key2 <= 51 Then
    MsgBox "Group 2"
ElseIf Key1 <= 96 And Key2 <= 96 Then
    MsgBox "Group 3"
Else
    MsgBox "Group 4"
End If

 

[Nomis_Eswod]

It doesn't matter what the key is, they will always do the same thing, it's just that the 'Do This' bit will only happen if they are from a different Key Group.

 

[al_b_cnu]

Hi,

You could introduce a 'Group' variable:

Select Case Key1
Case 10, 12
    Group1 = 1
Case 50, 51
    Group1 = 2
Case 94, 96
    Group1 = 3
Case 99
    Group1 = 4
End Select

Select Case Key2
Case 10, 12
    Group2 = 1
Case 50, 51
    Group2 = 2
Case 94, 96
    Group2 = 3
Case 99
    Group2 = 4
End Select

and test Group1 against Group2