我有一段簡單的代碼，可以將值從一個作業簿復制到另一個作業簿。我想根據用戶的輸入將值粘貼到不同的位置。例如，如果用戶輸入 Q1，則要粘貼的位置為 H5，Q2 為 I5，Q3 為 J5，Q4 為 K5。我可以添加四個 if 陳述句并復制它們下面的整個代碼，但必須有一個更簡單的方法。
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
uj5u.com熱心網友回復：
這里有兩個解決方案。一種使用來自用戶輸入的偏移值，另一種使用用戶輸入的選擇案例。我還對用戶輸入添加了一些驗證。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/405363.html
標籤：
上一篇：在VBAExcel中組合兩個宏