If variable 1 = 'this' then make variable 2 = 'that'

[carlala]

Hi all,

VBA coding is brand new to me today, I've got some experience on SAS but finding VBA a little tricky!

I cannot seem to find a tutorial to simply make a new variable depending on the contents of an existing variable.

My current code I'm trying does absolutely nothing:

Sub arms()

If Subject = "1" Then
   Arm = "1"

ElseIf Subject = "2" Then
   Arm = "1"

ElseIf Subject = "3" Then
   Arm = "1"
   
ElseIf Subject = "4" Then
   Arm = "2"
   
   ElseIf Subject = "5" Then
   Arm = "1"
   
   ElseIf Subject = "6" Then
   Arm = "1"
   
   ElseIf Subject = "7" Then
   Arm = "1"
   
   ElseIf Subject = "8" Then
   Arm = "2"
   
   ElseIf Subject = "9" Then
   Arm = "2"
   
    ElseIf Subject = "10" Then
   Arm = "2"
   
    ElseIf Subject = "11" Then
   Arm = "2"
   
    ElseIf Subject = "12" Then
   Arm = "1"
   
    ElseIf Subject = "13" Then
   Arm = "2"
   
    ElseIf Subject = "14" Then
   Arm = "1"
   
    ElseIf Subject = "15" Then
   Arm = "1"
   
    ElseIf Subject = "16" Then
   Arm = "1"
   
    ElseIf Subject = "17" Then
   Arm = "1"
   
    ElseIf Subject = "18" Then
   Arm = "2"
   
    ElseIf Subject = "19" Then
   Arm = "2"
   
    ElseIf Subject = "20" Then
   Arm = "2"
   
    ElseIf Subject = "21" Then
   Arm = "2"
   
    ElseIf Subject = "22" Then
   Arm = "1"
   
    ElseIf Subject = "23" Then
   Arm = "1"
   
    ElseIf Subject = "24" Then
   Arm = "1"
   
   ElseIf Subject = "25" Then
   Arm = "1"
   
    ElseIf Subject = "26" Then
   Arm = "2"
   
    ElseIf Subject = "27" Then
   Arm = "2"
   
   ElseIf Subject = "28" Then
   Arm = "2"
   
    ElseIf Subject = "29" Then
   Arm = "2"
   
    ElseIf Subject = "30" Then
   Arm = "2"
   
    ElseIf Subject = "31" Then
   Arm = "2"
   
    ElseIf Subject = "32" Then
   Arm = "1"
   
    ElseIf Subject = "33" Then
   Arm = "1"
   
    ElseIf Subject = "34" Then
   Arm = "1"
   
    ElseIf Subject = "35" Then
   Arm = "2"
   
    ElseIf Subject = "36" Then
   Arm = "2"
   
    ElseIf Subject = "37" Then
   Arm = "1"
   
    ElseIf Subject = "38" Then
   Arm = "2"
   
    ElseIf Subject = "39" Then
   Arm = "2"
   
    ElseIf Subject = "40" Then
   Arm = "1"

Else
   Arm = "0"

End If
End Sub

/*Should I be referencing the cell letters A/B rather than the variables I've assigned at the top? Do I need to account for the numeric format?
Untitled.jpg

^here is my worksheet

Thanks for any help given, it will really be appreciated!

[JKwan]

Well, I think this is what you mean.... give it a try

Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
' This function will fine the last row based on the Column that is sent to it.
    FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function
Sub arms()
    Dim lRow As Long
    Dim LastRow As Long
    
    LastRow = FindLastRow(ActiveSheet, "A")
    For lRow = 2 To LastRow
        Subject = ActiveSheet.Cells(lRow, "A")
        
        
        If Subject = "1" Then
            arm = "1"
            
            
            ElseIf Subject = "2" Then
            arm = "1"
            
            
            ElseIf Subject = "3" Then
            arm = "1"
            
            ElseIf Subject = "4" Then
            arm = "2"
            
            ElseIf Subject = "5" Then
            arm = "1"
            
            ElseIf Subject = "6" Then
            arm = "1"
            
            ElseIf Subject = "7" Then
            arm = "1"
            
            ElseIf Subject = "8" Then
            arm = "2"
            
            ElseIf Subject = "9" Then
            arm = "2"
            
            ElseIf Subject = "10" Then
            arm = "2"
            
            ElseIf Subject = "11" Then
            arm = "2"
            
            ElseIf Subject = "12" Then
            arm = "1"
            
            ElseIf Subject = "13" Then
            arm = "2"
            
            ElseIf Subject = "14" Then
            arm = "1"
            
            ElseIf Subject = "15" Then
            arm = "1"
            
            ElseIf Subject = "16" Then
            arm = "1"
            
            ElseIf Subject = "17" Then
            arm = "1"
            
            ElseIf Subject = "18" Then
            arm = "2"
            
            ElseIf Subject = "19" Then
            arm = "2"
            
            ElseIf Subject = "20" Then
            arm = "2"
            
            ElseIf Subject = "21" Then
            arm = "2"
            
            ElseIf Subject = "22" Then
            arm = "1"
            
            ElseIf Subject = "23" Then
            arm = "1"
            
            ElseIf Subject = "24" Then
            arm = "1"
            
            ElseIf Subject = "25" Then
            arm = "1"
            
            ElseIf Subject = "26" Then
            arm = "2"
            
            ElseIf Subject = "27" Then
            arm = "2"
            
            ElseIf Subject = "28" Then
            arm = "2"
            
            ElseIf Subject = "29" Then
            arm = "2"
            
            ElseIf Subject = "30" Then
            arm = "2"
            
            ElseIf Subject = "31" Then
            arm = "2"
            
            ElseIf Subject = "32" Then
            arm = "1"
            
            ElseIf Subject = "33" Then
            arm = "1"
            
            ElseIf Subject = "34" Then
            arm = "1"
            
            ElseIf Subject = "35" Then
            arm = "2"
            
            ElseIf Subject = "36" Then
            arm = "2"
            
            ElseIf Subject = "37" Then
            arm = "1"
            
            ElseIf Subject = "38" Then
            arm = "2"
            
            ElseIf Subject = "39" Then
            arm = "2"
            
            ElseIf Subject = "40" Then
            arm = "1"
            
            
        Else
            arm = "0"
            
            
        End If
        
        ActiveSheet.Cells(lRow, "B") = arm
    Next lRow
    
    
End Sub

[p45cal]

If you're wanting a macro to put values in column B, the following works on the active sheet:

Sub blah()
For Each cll In Range(Range("A2"), Range("A2").End(xlDown)).Cells
Select Case cll.Value
  Case 1, 2, 3, 5, 6, 7, 12, 14, 15, 16, 17, 22, 23, 24, 25, 32, 33, 34, 37, 40: Arm = 1
  Case 4, 8, 9, 10, 11, 13, 18, 19, 20, 21, 26, 27, 28, 29, 30, 31, 35, 36, 38, 39: Arm = 2
  Case Else: Arm = 0
End Select
cll.Offset(, 1).Value = Arm
Next cll
End Sub

If you're wanting a user defined function, then this function (in a standard code-module of the relevant book) will do it:

Function SubjToArm(Subj)
Select Case Subj
  Case 1, 2, 3, 5, 6, 7, 12, 14, 15, 16, 17, 22, 23, 24, 25, 32, 33, 34, 37, 40: SubjToArm = 1
  Case 4, 8, 9, 10, 11, 13, 18, 19, 20, 21, 26, 27, 28, 29, 30, 31, 35, 36, 38, 39: SubjToArm = 2
  Case Else: SubjToArm = 0
End Select
End Function

then in your example sheet you'd use the formula:
=SubjToArm(A2)
in cell B2, and copy down.

[SamT]

Pay close attention to the punctuation in P45cal's code. The ":" (Colon) reads as "The Next Line is"

Case 1, 2, 3, 5, 6, 7, 12, 14, 15, 16, 17, 22, 23, 24, 25, 32, 33, 34, 37, 40: SubjToArm = 1

Case 1, 2, 3, 5, 6, 7, 12, 14, 15, 16, 17, 22, 23, 24, 25, 32, 33, 34, 37, 40
SubjToArm = 1

p45cal is a very experienced VBA user, and you aren't yet. You can see that he has not Explicitly declared any variables. I strongly suggest that you put "Option Explicit" at the top of all your code pages. I set the VBA Options from the Tools menu >> Options >> Editor tab >> Code Settings box to all options checked.
Add the following to / edit his code to read

Sub blah() 
Dim cll As Range
    For Each...

Where Sub and arm are in fact Integer numbers

Function SubjToArm(Subj As Range) As Long

If they are words or Strings then

Function SubjToArm(Subj As Range) As String

And if unknown, then

Function SubjToArm(Subj As Range) As Variant
'Or
Function SubjToArm(Subj As Range)

Any variable not assigned a Type is auto-assigned the Variant Type. Variants can hold any type of value, including Objects, but are slower and take more memory.

[carlala]

Thank you very much all of you for your time and assistance, it really has helped! I ended up going for the user defined function approach by p45cal, consider this thread solved

[SamT]

I'll mark it solved this time, but at the top of the thread, you will see a Thread tools link, where you can mark your threads solved.