Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If X = Array?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If X = Array?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If X = Array?
Whatever I can use that avoids having 50+ if statements.
IE, this. Cheers! "Incidental" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If X = Array?
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 On Nov 8, 6:05 am, PaulW wrote: Whatever I can use that avoids having 50+ if statements. IE, this. Cheers! "Incidental" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming |