excel - 如何在列中查找值并将范围从其他工作表粘贴到其相邻列中

Question

我的项目的最终目标是用户将能够从 ComboBox 中选择一个值。在“摘要”选项卡上填写报告。该报告将包含 3、3 个单元格范围(在 3 个单独的工作表上分为 3 个 1x3 范围)。

我想找到用户在 ComboBox 中选择的值的行然后将该值右侧的 9 个单元格设置为等于前面提到的范围内的值。

我尝试了几种不同的方法，但我将在下面包含我最近处理的代码:

Private Sub OKButton1_Click()
Dim userValue, rangeOne, rangeTwo, rangeThree
Dim i As Long

i = 4


userValue = ComboBox1.Value
Set rangeOne = Sheets("Sheet2").Range(Range("F23:H23")
Set rangeTwo = Sheets("Sheet3").Range("F90:H90")
Set rangeThree = Sheets("Sheet4").Range("F17:H17")



While Sheets("Reports").Range(cells(i,1)).Value <> "" 
      If Sheets("Reports").Range(cells(i, "A")).Value = "userValue" Then

         Set Sheets("Reports").Range(Cells(i, "B:E")) = rangeOne
         Set Sheets("Reports").Range(Cells(i, "F:I")) = rangeOne
         Set Sheets("Reports").Range(Cells(i, "J:M")) = rangeOne
      End If
      i = i + 1
Wend
Unload UserForm2
End Sub 

关于如何改进或让它发挥作用的任何想法？当前出现 1004 错误。

Answer 1

使用 excel 时的两个建议:

总是 为您需要使用的每张纸/书制作变量

如果可以，请避免使用范围和对象。像我在下面所做的那样，使用数组和 for 循环遍历单个单元格要容易得多。

我对您到底需要做什么有点困惑，因此您需要稍微修改一下以适合您的范围/您希望数据去往的地方。如果您感到困惑或需要进一步的帮助，请告诉我，我会更新此信息。

Dim userValue
Dim xrow As Long, ws1 As Worksheet, ws2 As Worksheet, ws3 as Worksheet, ws4 as Worksheet
Dim arrData() as variant

set ws1 = Worksheets("Report")
set ws2 = Worksheets("Sheet2")
set ws3 = Worksheets("Sheet3")
set ws4 = Worksheets("Sheet4")

userValue = ComboBox1.Value
xrow = 1

ws2.activate
'the InStr function checks if the first condition contains the second, and when it does, it returns 1, which in turn triggers the if statement
for x = 1 To ws2.Cells(rows.count, 1).end(xlup).row
    if InStr(1, Cells(x, 1), userValue) > 0 Then
        arrData(0) = ws2.Cells(x, 2).value
        arrData(1) = ws2.Cells(x, 3).value
        arrData(2) = ws2.Cells(x, 4).value
    else:
    end if
next x

ws3.activate
for x = 1 To ws3.Cells(rows.count, 1).end(xlup).row
    if InStr(1, Cells(x, 1), userValue) > 0 Then
        arrData(3) = ws3.Cells(x, 2).value
        arrData(4) = ws3.Cells(x, 3).value
        arrData(5) = ws3.Cells(x, 4).value
    else:
    end if
next x

ws4.activate
for x = 1 To ws4.Cells(rows.count, 1).end(xlup).row
    if InStr(1, Cells(x, 1), userValue) > 0 Then
        arrData(6) = ws4.Cells(x, 2).value
        arrData(7) = ws4.Cells(x, 3).value
        arrData(8) = ws4.Cells(x, 4).value
    else:
    end if
next x

ws1.activate
ws1.Cells(xrow, 1) = userValue
for y = 0 To 8
    ws1.Cells(xrow, y+1).value = arrData(y)
next y
xrow = xrow + 1