我在单元格A1上有一个列表要在工作表“此处输入”上找到，并且在单元格A1到A692的“字符串列表”工作表上有完整的字符串值列表，该列表已存储在数组中svr。我要发生的是宏，以检查工作表“此处输入”上A列上的所有值，并将其与array内的值进行比较svr，直到找到匹配项为止，然后逐个比较范围。工作表“字符串列表”到工作表“此处输入”的单元格。我已经尝试了下面的代码，但我认为它需要做更多的工作。
Sub Main_SvrLst()
Dim inp As Worksheet
Dim lst As Worksheet
Dim svr(691) As String
Set inp = ThisWorkbook.Sheets("Input Here")
Set lst = ThisWorkbook.Sheets("String List")
lr = inp.Cells(Rows.Count, 1).End(xlUp).Row
For svrctr = 0 To 691
svr(svrctr) = lst.Range("A2").Offset(svrctr, 0).Value
Next svrctr
For a = 2 To lr
If inp.Cells(a, 1) = svr(a) Then
Worksheets("String List").Activate
lst.Range(Cells(a, 2), Cells(a, 8)).Copy
inp.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial
Worksheets("Input Here").Activate
End If
Next a
End Sub
我已更改了您的一些命名，以使您的身份更清晰。
也关闭您Array的以获得正确引用的数字
Sub Main_SvrLst()
Dim inp As Worksheet
Dim lst As Worksheet
Dim svr(691) As String
Set inp = ThisWorkbook.Sheets("Input Here")
Set lst = ThisWorkbook.Sheets("String List")
Dim LastRowOfInputHere As Long
LastRowOfInputHere = inp.Cells(Rows.Count, 1).End(xlUp).Row
Dim svrctr As Long
'Made it 2 to 691 to off set your Header Row
'This way the Array position and the row number are the same
For svrctr = 2 To 691
svr(svrctr) = lst.Cells(svrctr, "A").Value
Next svrctr
Dim InputHereRowReference As Long
Dim StringListArrayReference As Long
'With your original text it was comparing a two row offset between the "Input" and "String" Sheets
For InputHereRowReference = 2 To LastRowOfInputHere
For StringListArrayReference = 2 To 691
If inp.Cells(InputHereRowReference, 1) = svr(StringListArrayReference) Then
lst.Activate
lst.Range(Cells(StringListArrayReference, 2), Cells(StringListArrayReference, 8)).Copy
inp.Activate
inp.Cells(InputHereRowReference, 2).PasteSpecial
End If
Next StringListArrayReference
Next InputHereRowReference
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句