If X = Array?

[Guest]

I have a report that contains a list of account codes in column A, and I'm
trying to build a userform, that can give performance figures by department /
company / section.

So what I need to do, is have the userform rename the account codes, by
adding a suffix to define which department they belong to. What I have so far
is something along the lines of:

If Left(Cells(i, 1), 6) = "AAL060" Then n = 1
If Left(Cells(i, 1), 6) = "AAL064" Then n = 1

But the list of account codes is about 50 long, and I don't want 50 lines of
if statements if I can avoid it.

Is there some way I can have :

If Left(Cells(i, 1), 6) = "AAL060"&"AAL064"&"PFF800" Then n = 1

So its only 1 check against a list?

 

[Incidental]

Hi Paul

i'm not sure if an array is the easiest way round this sort of of
problem or not but a quick and painless way to do it would be to use
select case on the range of cells holding the account codes the code
below should give you an idea of what i mean

Option Explicit
Dim n As Integer
Dim MyRng, MyCell As Range
Private Sub CommandButton1_Click()

Set MyRng = [A1:A50]

For Each MyCell In MyRng

Select Case MyCell.Value

Case "AAL060", "AAL064", "PFF800"
n = 1
Case "AAL062", "AAL067", "PFF804"
n = 2

End Select

'add your code here

Next MyCell

End Sub

hope this helps you out

Steve

 

[Guest]

Whatever I can use that avoids having 50+ if statements.

IE, this. Cheers!

 

[SteveM]

It would be easiest to create an auxiliary sheet or column with your
account codes and an adjacent column (hidden perhaps) with the suffix
for each code. Then use a VLOOKUP to find the suffix in a single line
of code.

Suffix = Application.WorksheetFunction.VLookup(CodeVal, LookUp Range,
2)

Where the Code Value (Code Val) is passed to the function.

SteveM

Whatever I can use that avoids having 50+ if statements.

IE, this. Cheers!

Hi Paul

i'm not sure if an array is the easiest way round this sort of of
problem or not but a quick and painless way to do it would be to use
select case on the range of cells holding the account codes the code
below should give you an idea of what i mean

Option Explicit
Dim n As Integer
Dim MyRng, MyCell As Range
Private Sub CommandButton1_Click()

Set MyRng = [A1:A50]

For Each MyCell In MyRng

Select Case MyCell.Value

Case "AAL060", "AAL064", "PFF800"
n = 1
Case "AAL062", "AAL067", "PFF804"
n = 2

End Select

'add your code here

Next MyCell

End Sub

hope this helps you out

Steve