Elseif help. Need help reducing code. "Beginner"

  • Hello All
    Hope you are all keeping well.

    My knowledge with VBA is very limited and i need help reducing code.

    This is what i have sover:

    Sub split() '// This sub takes the first 3 char from each cell in range and offsets that value to the next column
    Dim SrchRng As Range, cel As Range
    Set SrchRng = Range("A1:A8")

    For Each cel In SrchRng
    cel.Offset(0, 1).Value = Left(cel.Value, 3)
    Next cel
    End Sub

    Sub AddName() ' // This sub then checks the cells in the range and allocates a Name to the next column
    Dim SrchRng As Range, cel As Range
    Set SrchRng = Range("B1:B8")

    For Each cel In SrchRng
    If InStr(1, cel.Value, "BEE") > 0 Then
    cel.Offset(0, 1).Value = "Dr Beeton"
    ElseIf InStr(1, cel.Value, "BET") > 0 Then
    cel.Offset(0, 1).Value = "Dr Bettings"
    cel.Offset(0, 1).Value = "Error"
    End If
    Next cel
    End Sub[/VBA]

    Where i need help is, i have over 80 abbreviation(BEE,BET) than needs to be assigned to specific Names. I can just ElseIf my way though all of the abbreviations and it should work, however, i feel there should be a better way of getting though all those without having 80 ElseIf statements.

    This is the results after running the Subs with static data being in Range("A1:A8")
    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tSheet1.JPG Views:\t1 Size:\t21.3 KB ID:\t1207680","data-align":"none","data-attachmentid":"1207680","data-size":"full","title":"Sheet1.JPG"}[/ATTACH]

    I would appreciate any help regarding this...thx yall

  • Please paste code between html code tags. You can click the # icon in toolbar to insert them.

    You can easily add the Left() part in the Case.

  • Why not have a range with the value pairs and just use an Excel lookup function? That would be a lot easier to maintain (particularly for a user) than hard-coding the values in VBA (and you might not need VBA at all.)

  • Another way (see attached)


  • Morning All.

    Thank you for all the advice on this, will try all options to find the best fit for what i want to achieve.

    I'm exciting to implement all options and learn on the way.

    One thing that popped up in my head this morning was the following:

    The Abbreviations(BEE, BET) and the corresponding names are all fixed data.

    If i make two columns with all abbr . and corresponding names in alphabetic order. Could i then read all that into separate ARRAY's. This would then have Array.abbr and Array.Name having corresponding numbers for both values.

    Then we can check the cell.value against the Array.abbr and when it finds a match we can use that array number to fetch the corresponding Array.Name.

    This would help with any changes that needs to be made with the fixed data without changing much of the code.

    Thank all, hope you have a pleasant day.

  • [USER="5841"]jolivanes[/USER] haha :angrypc:...i told myself the same thing when downloading your file and seeing what you have provided.

    Thank you again for taking the time to help in this regard.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!