AxelAxel99
New Member
- Joined
- May 31, 2015
- Messages
- 1
I do not have too much experience with VBA, however I believe I am very close to getting this VBA macro up and running for this spreadsheet I created.
Essentially, (B2, I2) populates via formulas in my spreadsheet. (J2), I am trying to get it so that If I type in the numbers 148,149,150,157 etc all the way to 168, the information in range (B2, I2) will paste values to the correct ranges, which I have set. Any other numbers or text strings that are input to J2, I would like to revert them to my "YardMove" sub, which I will not build until I get this running.
For some reason, this macro, so far, only works if I use 148. The rest of the doors will not populate if I try to use them. Is there something wrong with my nested if statements?
Sub SendDoor()
Dim SelectDoor As Integer
Dim CheckCell As Integer
Dim Quit As Boolean
Dim DOOR148 As Range
Dim DOOR149 As Range
Dim DOOR150 As Range
Dim DOOR157 As Range
Dim DOOR158 As Range
Dim DOOR159 As Range
Dim DOOR160 As Range
Dim DOOR161 As Range
Dim DOOR162 As Range
Dim DOOR163 As Range
Dim DOOR164 As Range
Dim DOOR165 As Range
Dim DOOR166 As Range
Dim DOOR167 As Range
Dim DOOR168 As Range
Set DOOR148 = ActiveSheet.Range("B7")
Set DOOR149 = ActiveSheet.Range("B8")
Set DOOR150 = ActiveSheet.Range("B9")
Set DOOR157 = ActiveSheet.Range("B10")
Set DOOR158 = ActiveSheet.Range("B11")
Set DOOR159 = ActiveSheet.Range("B12")
Set DOOR160 = ActiveSheet.Range("B13")
Set DOOR161 = ActiveSheet.Range("B14")
Set DOOR162 = ActiveSheet.Range("B15")
Set DOOR163 = ActiveSheet.Range("B16")
Set DOOR164 = ActiveSheet.Range("B17")
Set DOOR165 = ActiveSheet.Range("B18")
Set DOOR166 = ActiveSheet.Range("B19")
Set DOOR167 = ActiveSheet.Range("B20")
Set DOOR168 = ActiveSheet.Range("B21")
Sheets("Handoff").Select
ActiveSheet.Range("J2").Select
SelectDoor = Selection.Text()
Quit = False 'Do not stop process
If SelectDoor >= 148 And SelectDoor <= 150 Then 'If door number Do not quit
Quit = False
ElseIf SelectDoor >= 157 And SelectDoor <= 168 Then
Quit = False
Else: Quit = True
End If
If Quit = True Then 'If quit, yard move process
Call YardMove
ElseIf Quit = False Then 'Otherwise
End If
If SelectDoor = 148 Then
Range("B2", "I2").Select
Selection.Copy
DOOR148.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 149 Then
Range("B2", "I2").Select
Selection.Copy
DOOR149.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 150 Then
Range("B2", "I2").Select
Selection.Copy
DOOR150.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 157 Then
Range("B2", "I2").Select
Selection.Copy
DOOR157.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 158 Then
Range("B2", "I2").Select
Selection.Copy
DOOR158.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 159 Then
Range("B2", "I2").Select
Selection.Copy
DOOR159.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 160 Then
Range("B2", "I2").Select
Selection.Copy
DOOR160.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 161 Then
Range("B2", "I2").Select
Selection.Copy
DOOR161.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 162 Then
Range("B2", "I2").Select
Selection.Copy
DOOR162.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 163 Then
Range("B2", "I2").Select
Selection.Copy
DOOR163.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 164 Then
Range("B2", "I2").Select
Selection.Copy
DOOR164.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 165 Then
Range("B2", "I2").Select
Selection.Copy
DOOR165.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 166 Then
Range("B2", "I2").Select
Selection.Copy
DOOR166.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 167 Then
Range("B2", "I2").Select
Selection.Copy
DOOR167.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 168 Then
Range("B2", "I2").Select
Selection.Copy
DOOR168.Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Sub YardMove()
Sheets("Handoff").Select
ActiveSheet.Range("B2:J2").Select
End Sub
Essentially, (B2, I2) populates via formulas in my spreadsheet. (J2), I am trying to get it so that If I type in the numbers 148,149,150,157 etc all the way to 168, the information in range (B2, I2) will paste values to the correct ranges, which I have set. Any other numbers or text strings that are input to J2, I would like to revert them to my "YardMove" sub, which I will not build until I get this running.
For some reason, this macro, so far, only works if I use 148. The rest of the doors will not populate if I try to use them. Is there something wrong with my nested if statements?
Sub SendDoor()
Dim SelectDoor As Integer
Dim CheckCell As Integer
Dim Quit As Boolean
Dim DOOR148 As Range
Dim DOOR149 As Range
Dim DOOR150 As Range
Dim DOOR157 As Range
Dim DOOR158 As Range
Dim DOOR159 As Range
Dim DOOR160 As Range
Dim DOOR161 As Range
Dim DOOR162 As Range
Dim DOOR163 As Range
Dim DOOR164 As Range
Dim DOOR165 As Range
Dim DOOR166 As Range
Dim DOOR167 As Range
Dim DOOR168 As Range
Set DOOR148 = ActiveSheet.Range("B7")
Set DOOR149 = ActiveSheet.Range("B8")
Set DOOR150 = ActiveSheet.Range("B9")
Set DOOR157 = ActiveSheet.Range("B10")
Set DOOR158 = ActiveSheet.Range("B11")
Set DOOR159 = ActiveSheet.Range("B12")
Set DOOR160 = ActiveSheet.Range("B13")
Set DOOR161 = ActiveSheet.Range("B14")
Set DOOR162 = ActiveSheet.Range("B15")
Set DOOR163 = ActiveSheet.Range("B16")
Set DOOR164 = ActiveSheet.Range("B17")
Set DOOR165 = ActiveSheet.Range("B18")
Set DOOR166 = ActiveSheet.Range("B19")
Set DOOR167 = ActiveSheet.Range("B20")
Set DOOR168 = ActiveSheet.Range("B21")
Sheets("Handoff").Select
ActiveSheet.Range("J2").Select
SelectDoor = Selection.Text()
Quit = False 'Do not stop process
If SelectDoor >= 148 And SelectDoor <= 150 Then 'If door number Do not quit
Quit = False
ElseIf SelectDoor >= 157 And SelectDoor <= 168 Then
Quit = False
Else: Quit = True
End If
If Quit = True Then 'If quit, yard move process
Call YardMove
ElseIf Quit = False Then 'Otherwise
End If
If SelectDoor = 148 Then
Range("B2", "I2").Select
Selection.Copy
DOOR148.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 149 Then
Range("B2", "I2").Select
Selection.Copy
DOOR149.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 150 Then
Range("B2", "I2").Select
Selection.Copy
DOOR150.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 157 Then
Range("B2", "I2").Select
Selection.Copy
DOOR157.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 158 Then
Range("B2", "I2").Select
Selection.Copy
DOOR158.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 159 Then
Range("B2", "I2").Select
Selection.Copy
DOOR159.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 160 Then
Range("B2", "I2").Select
Selection.Copy
DOOR160.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 161 Then
Range("B2", "I2").Select
Selection.Copy
DOOR161.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 162 Then
Range("B2", "I2").Select
Selection.Copy
DOOR162.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 163 Then
Range("B2", "I2").Select
Selection.Copy
DOOR163.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 164 Then
Range("B2", "I2").Select
Selection.Copy
DOOR164.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 165 Then
Range("B2", "I2").Select
Selection.Copy
DOOR165.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 166 Then
Range("B2", "I2").Select
Selection.Copy
DOOR166.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 167 Then
Range("B2", "I2").Select
Selection.Copy
DOOR167.Select
Selection.PasteSpecial Paste:=xlPasteValues
If SelectDoor = 168 Then
Range("B2", "I2").Select
Selection.Copy
DOOR168.Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Sub YardMove()
Sheets("Handoff").Select
ActiveSheet.Range("B2:J2").Select
End Sub