Pasting values from generated cells to chart below via formula

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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Your nested if statements are nested incorrectly. If you must use them use them like this:
Code:
If SelectDoor = 148 Then
    Range("B2", "I2").Select
    Selection.Copy
    DOOR148.Select
    Selection.PasteSpecial Paste:=xlPasteValues
Else
    If SelectDoor = 149 Then
        Range("B2", "I2").Select
        Selection.Copy
        DOOR149.Select
        Selection.PasteSpecial Paste:=xlPasteValues
    Else
        If SelectDoor = 150 Then
            Range("B2", "I2").Select
            Selection.Copy
            DOOR150.Select
            Selection.PasteSpecial Paste:=xlPasteValues
        Else
            'More here
            
            
        End If
    End If
End If

Select case would be better in most instances, and it would allow you to get rid of some of the lines above your nested if statements:
Code:
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 = Range("J2").Text
    
    Select Case CLng(SelectDoor)
    Case 148
        Range("B2", "I2").Copy
        DOOR148.PasteSpecial Paste:=xlPasteValues
    Case 149
        Range("B2", "I2").Copy
        DOOR149.PasteSpecial Paste:=xlPasteValues
    Case 150
        Range("B2", "I2").Copy
        DOOR150.PasteSpecial Paste:=xlPasteValues
    Case 157
        Range("B2", "I2").Copy
        DOOR157.PasteSpecial Paste:=xlPasteValues
    Case 158
        Range("B2", "I2").Copy
        DOOR158.PasteSpecial Paste:=xlPasteValues
    'Add more here
    Case Else
        Call YardMove
    End Select
    

End_Sub:

End Sub

Is there a reason you have to declare all of the ranges? It is not necessary as far as I can see.
 
Upvote 0
Code:
Option Explicit

Sub SendDoor()

    Dim SelectDoor As Integer
    Dim CheckCell As Integer

    Dim aryDoor As Variant
    Dim aryAddr As Variant
    Dim lIndex As Long
    Dim sAddress As String
    
    aryDoor = Array(148, 149, 150, 157, 158, 159, 160, 161, 162, 163, _
        164, 165, 166, 167, 168)
    aryAddr = Array("B7", "B8", "B9", "B10", "B11", "B12", "B13", "B14", "B15", "B16", _
        "B17", "B18", "B19", "B20", "B21")

    Sheets("Handoff").Select
    ActiveSheet.Range("J2").Select
    SelectDoor = Range("J2").Text
    
    Select Case CLng(SelectDoor)
    Case 148 To 150, 157 To 168
        lIndex = Application.WorksheetFunction.Match(SelectDoor, aryDoor, 0)
        sAddress = Application.WorksheetFunction.Index(aryAddr, lIndex)
        Range("B2", "I2").Copy
        Range(sAddress).PasteSpecial Paste:=xlPasteValues
    Case Else
        Call YardMove
    End Select

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top