If语句用于两个单独工作表中的范围

Question

我有一个工作表，其中一列填写从1到4的数字，另一列填写公司名称。

我对另一个工作表感兴趣，我只看到数字为3或更大的公司。因此，要使IF语句可以做到这一点，由于有一个大的和不断变化的数据库。然而，我一直得到一个错误，我提出的宏。

我希望有人能帮助我。

Sub Simple_if()
Dim score As Integer, i As Long
i = 1
Do While Worksheets("Business").cells(i, "D").Value
score = Worksheets("Business").cells(i, "D").Value
If score >= 3 Then
Worksheets("Engagement Plan").cells(i, "A4").Value = Worksheets("Business").cells(i, "C").Value

End If
i = i + 1
Loop
End Sub

Answer 1

请尝试下一个代码:

Sub Simple_if()
Dim shB As Worksheet, shE As Worksheet, lastRB As Long
Dim score As Integer, i As Long, k As Long, arrB, arrE
Set shB = Worksheets("Business")
Set shE = Worksheets("Engagement Plan")
lastRB = shB.Range("D" & shB.rows.count).End(xlUp).row
arrB = shB.Range("C1:D" & lastRB).Value 'put the shB sheet range in an array
ReDim arrE(UBound(arrB))      'redim arrE at maximum possible dimension
For i = 1 To UBound(arrB)
If arrB(i, 2) >= 3 Then
arrE(k) = arrB(i, 1) 'fill arrE only with elements >=3
k = k + 1
End If
Next i
ReDim Preserve arrE(k - 1)  'redim the array to keep only the filled elements
'drop the array content at once:
shE.Range("A1").Resize(UBound(arrE) + 1, 1).Value = WorksheetFunction.Transpose(arrE)
End Sub