VBA Excel:在两个不同的工作表之间查找匹配项

Question

我写了一些代码，试图匹配工作表上的ID("ISIN"(；A并确认它是否是工作表"A"B.如果有匹配，我想在工作表上取相应的输入/值"；B"；并将其放置在工作表"上；A"；。到目前为止，我的状况不允许我继续前进，vlookup也不起作用。

有什么建议吗？

'For loop to find the corresponding inputs associated with the ISIN
Dim rgRow As Range
For Each rgRow In datarange.Rows

sISIN = rgRow.Cells(1, 2)
sInstrument = rgRow.Cells(1, 3)
sType = rgRow.Cells(1, 4)
sRegion = rgRow.Cells(1, 6)
sAssetClass = rgRow.Cells(1, 7)
sRating = rgRow.Cells(1, 8)
sRatingDynamic = rgRow.Cells(1, 9)
sProfil = rgRow.Cells(1, 10)
ISINmatch = Application.Match(sISIN, listrange.Columns(1), 0)

If sType = "Securities" And sISIN <> "" And Not IsError(ISINmatch) Then

sInstrument = Application.WorksheetFunction.VLookup(sISIN, listrange, 2, False)
sRegion = Application.WorksheetFunction.VLookup(sISIN, listrange, 3, False)
sAssetClass = Application.WorksheetFunction.VLookup(sISIN, listrange, 4, False)
sRating = Application.WorksheetFunction.VLookup(sISIN, listrange, 5, False)
sRatingDynamic = Application.WorksheetFunction.VLookup(sISIN, listrange, 7, False)
sProfil = Application.WorksheetFunction.VLookup(sISIN, listrange, 6, False)

End If
Next rgRow

Answer 1

不清楚代码中的某些范围(例如DataRange和listrange(指的是什么，但当找到sISIN时，此代码应将相关值放在工作表上。

注意，您并不真正需要变量，您可以将所有Application.Vlookup调用的结果直接分配给单元格。

此外，我在一条注释中建议不要使用Application.Vlookup，因为Application.Match将返回行sISIN，您可以使用它来获得所需的值。

我仍然建议使用它，但不幸的是，由于代码中使用的范围存在上述问题，我无法真正发布任何代码来展示它的工作方式。

不管怎样，这是代码。

'For loop to find the corresponding inputs associated with the ISIN
Dim rgRow As Range
For Each rgRow In DataRange.Rows

sISIN = rgRow.Cells(1, 2)
sType = rgRow.Cells(1, 4).Value

If sType = "Securities" And sISIN <> "" Then

ISINmatch = Application.Match(sISIN, listrange.Columns(1), 0)

If Not IsError(ISINmatch) Then

sInstrument = Application.WorksheetFunction.VLookup(sISIN, listrange, 2, False)
sRegion = Application.WorksheetFunction.VLookup(sISIN, listrange, 3, False)
sAssetClass = Application.WorksheetFunction.VLookup(sISIN, listrange, 4, False)
sRating = Application.WorksheetFunction.VLookup(sISIN, listrange, 5, False)
sRatingDynamic = Application.WorksheetFunction.VLookup(sISIN, listrange, 7, False)
sProfil = Application.WorksheetFunction.VLookup(sISIN, listrange, 6, False)

rgRow.Cells(1, 3).Value = sInstrument
rgRow.Cells(1, 4).Value = sType
rgRow.Cells(1, 6).Value = sRegion
rgRow.Cells(1, 7).Value = sAssetClass
rgRow.Cells(1, 8).Value = sRating
rgRow.Cells(1, 9).Value = sRatingDynamic
rgRow.Cells(1, 10).Value = sProfil
End If
End If
Next rgRow