Paste location based on the input of the user-CodePudding

I have a simple piece of code that copies a value from one workbook to another. I want to paste the value in a different location based on the user's input. For example, if the user inputs Q1, the location to paste will be H5, Q2 will be I5, Q3 will be J5, and Q4 will be K5. I could add four if statements and copy the entire code below them, but there has to be a simpler way.


Dim wb As Workbook
Set wb = Workbooks.Open("workbooks path")

Dim myValue As Variant
myValue = InputBox("Choose Quarter")

Dim v As Variant, v1 As Variant

Dim i As String
Set i = myValue

v = wb.Worksheets("Master").Range("J6").Value
ThisWorkbook.Worksheets("Sheet 1").Range("H5").Value = v 'if Q1 then H5, Q2 I, Q3 J, Q4 K

v1 = wb.Worksheets("Master").Range("J7").Value
ThisWorkbook.Worksheets("Sheet 2").Range("H5").Value = v1 'if Q1 then H5, Q2 I, Q3 J, Q4 K

CodePudding user response：

Here are two solutions. One uses an offset value from user input the other uses a select case on the user input. I also added in some validation on the user input.

Dim wb As Workbook
Set wb = Workbooks.Open("workbooks path")

Dim valbool As Boolean
valbool = False
Dim myValue As Variant
Do Until valbool 'This will make sure the value entered is valid
    myValue = InputBox("Choose Quarter")
    
    If Left(myValue, 1) = "Q" And Right(myValue, 1) > 0 And Right(myValue, 1) <= 4 Then
        valbool = True
    Else
        MsgBox "Please enter in format Q1, Q2, Q3, Q4"
    End If
Loop

Dim v As Variant, v1 As Variant

'Dim i As String
'i = myValue '''only objects get set, but you can just use myValue here

v = wb.Worksheets("Master").Range("J6").Value
ThisWorkbook.Worksheets("Sheet 1").Cells(5, Right(myValue, 1)   7).Value = v 'if Q1 then H5, Q2 I, Q3 J, Q4 K

v1 = wb.Worksheets("Master").Range("J7").Value
ThisWorkbook.Worksheets("Sheet 2").Cells(5, Right(myValue, 1)   7).Value = v1 'if Q1 then H5, Q2 I, Q3 J, Q4 K

    Dim wb As Workbook
    Set wb = Workbooks.Open("workbooks path")
    
    Dim valbool As Boolean
    valbool = False
    Dim myValue As Variant
    Do Until valbool 'This will make sure the value entered is valid
        myValue = InputBox("Choose Quarter")
        
        If Left(myValue, 1) = "Q" And Right(myValue, 1) > 0 And Right(myValue, 1) <= 4 Then
            valbool = True
        Else
            MsgBox "Please enter in format Q1, Q2, Q3, Q4"
        End If
    Loop
    
    Dim v As Variant, v1 As Variant
    
    'Dim i As String
    'Set i = myValue '''only objects get set, but you can just use myValue here
    
    v = wb.Worksheets("Master").Range("J6").Value
    v1 = wb.Worksheets("Master").Range("J7").Value
    Select Case Right(myValue, 1)
        Case 1
            ThisWorkbook.Worksheets("Sheet 1").Cells(5, 8).Value = v
            ThisWorkbook.Worksheets("Sheet 2").Cells(5, 8).Value = v1
        Case 2
            ThisWorkbook.Worksheets("Sheet 1").Cells(5, 9).Value = v
            ThisWorkbook.Worksheets("Sheet 2").Cells(5, 9).Value = v1
        Case 3
            ThisWorkbook.Worksheets("Sheet 1").Cells(5, 10).Value = v
            ThisWorkbook.Worksheets("Sheet 2").Cells(5, 10).Value = v1
        Case 4
            ThisWorkbook.Worksheets("Sheet 1").Cells(5, 11).Value = v
            ThisWorkbook.Worksheets("Sheet 2").Cells(5, 11).Value = v1
    End Select