IF Statement in Excel VBA

[floridagunner]

Hello All,

I need help with an IF statement in my VBA code. I cannot use a regular IF formula as I have more than seven conditions. What I need the code to do is return a letter value in cell B2 based on the value in cell S2. So if the value in cell S2 is "M.00441" cell B2 should return A. If the value in cell S2 is "M.00442" cell B2 should return the letter "B". This need to be repeated all the way to "M.00449" in column B

Any help would be appreciated.

 

[hroush]

You want to use a case statement:

MS Excel: CASE Statement (VBA)

 

[floridagunner]

Hello Hroush,

Thank you for your response. The link that you provided is informative but I cant figure out how to apply it to my situation. Below is the code that I have compiled so far (It is however incomplete):

Sub TestFunction()

Dim myRange As Range
Dim myCell1 As Range

Sheets("SAP Refined Data").Select
Set myRange = Range("1:1")
Set myCell1 = myRange.Find(What:="Sub-Program")
myCell1.Select
ActiveCell.Offset(1, 0).Select
If Range("S2").Value = "M.00441" Then Range("B2").Value = "A" Else
If Range("S2").Value = "M.00442" Then Range("B2").Value = "B" Else
If Range("S2").Value = "M.00443" Then Range("B2").Value = "C" Else
If Range("S2").Value = "M.00444" Then Range("B2").Value = "D" Else

I have not yet finished writing out the conditions so I'm not sure if the code above will even work, but I see another problem. Since I'm not actually using a formula I can't do what I would normally do which is instruct VBA to copy the formula in cell B2 down to the end of the table. How can I apply the above code to all the cells in column B that have a corresponding value in column S?

 

[hroush]

select case Range("S2").value

case "M.00441"

Range("B2").Value = "A"
case "M.00442"
Range("B2").Value = "B"
case "M.00443"
Range("B2").Value = "C"
...
End Select

</pre>

 

[Michael M]

Ok, try this.....UNTESTED

Sub test()
Dim num As Integer
Range("b2").Formula = "=Right(S2, 1)"
Select Case Range("B2").Value
    Case Is = 1
    Range("B2").Value = "a"
    Case Is = 2
    Range("B2").Value = "b"
    Case Is = 3
    Range("B2").Value = "c"
    Case Is = 4
    Range("B2").Value = "d"
    Case Is = 5
    Range("B2").Value = "e"
    Case Is = 6
    Range("B2").Value = "f"
    Case Is = 7
    Range("B2").Value = "g"
    Case Is = 8
    Range("B2").Value = "h"
    Case Is = 9
    Range("B2").Value = "i"
End Select
End Sub

 

[floridagunner]

Hroush and MichaelM, thank you for your help and apologies for the late reply. I got diverted to another project.

Michael, I tried your code after making a slight edit, but it's returning everything as "A". I believe that is probably because the value in S2 is
"M.00441".

Sub Test1()
Dim num As Integer
Range("Y2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Formula = "=Right(S2, 7)"
Select Case Range("S2").Value
    Case Is = "M.00441"
    Selection.Value = "A"
    Case Is = "M.00442"
    Selection.Value = "B"
    Case Is = "M.00443"
    Selection.Value = "C"
    Case Is = "M.00444"
    Selection.Value = "D"
    Case Is = "M.00445"
    Selection.Value = "E"
    Case Is = "M.00446"
    Selection.Value = "F"
    Case Is = "M.00447"
    Selection.Value = "G"
    Case Is = "M.00448"
    Selection.Value = "H"

End Select
End Sub

I need the code to return the corresponding letter in all the rows after referencing the "M.0044X" number and not just return A. Also this code is populating the letter "A" all the way down to the end of the worksheet, when the table range only extends to row number 18539. Any ideas on how I can fix it so it only populates within the table range. I changed the column to Y as I wanted to test the macro before I populated column B.

Thanks again for your help guys.

 

[Michael M]

Maybe

Sub Test1()
Dim num As Integer, lr As Long, r As Long
lr = Cells(Rows.Count, "S").End(xlUp).Row
For r = 2 To lr
Select Case Range("S" & r).Value
    Case Is = "M.00441"
    Range("Y" & r).Value = "A"
    Case Is = "M.00442"
    Range("Y" & r).Value = "B"
    Case Is = "M.00443"
    Range("Y" & r).Value = "C"
    Case Is = "M.00444"
    Range("Y" & r).Value = "D"
    Case Is = "M.00445"
    Range("Y" & r).Value = "E"
    Case Is = "M.00446"
    Range("Y" & r).Value = "F"
    Case Is = "M.00447"
    Range("Y" & r).Value = "G"
    Case Is = "M.00448"
    Range("Y" & r).Value = "H"
End Select
Next r
End Sub

 

[floridagunner]

Thank You MichaelM. It works perfectly

 

[Michael M]

Glad to hear.....thanks for the feedback....