PDA

View Full Version : [SOLVED] If variable 1 = 'this' then make variable 2 = 'that'



carlala
10-07-2014, 09:40 AM
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?
12369

^here is my worksheet :)

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

JKwan
10-07-2014, 11:45 AM
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
10-07-2014, 11:47 AM
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
10-07-2014, 12:21 PM
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
10-07-2014, 12:49 PM
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 :D

SamT
10-07-2014, 05:14 PM
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.