根据匹配结果将值从wkbk 1返回到wkbk 2

Question

我在下面评论了我的代码。我正在尝试循环浏览当前工作簿，以搜索具有蓝色边框的单元格(我目前只使用Col a进行测试(。

如果单元格有蓝色边框，那么我想在第二个工作簿上找到匹配的单元格(范围始终为a列，第二个工作薄应该始终有匹配的值(。

当找到匹配项时，我想将第一个工作簿(包括格式(中的值返回到第二个工作簿，该工作簿位于找到匹配项的同一行，但位于下一个可用列中。大多数时候，它只是B列，但如果B列被填满，则转到C列等。(

匹配函数工作并返回正确的idCella.value。

resultM表示找到了匹配的正确行，但我不确定如何继续。

我知道我需要.offset(0,1)结果m，但我遗漏了一些东西，我不确定是什么。

希望描述能有所帮助，但如果你需要更多信息，请告诉我！

编辑：当我说找到完全匹配的值时，第二个工作簿上的值将不会有相同的蓝色边框/interior.color。我只想找到单元格的匹配值。这可能是多余的，但我想我会添加它。我仍在学习：(。

Dim testWS As Worksheet
Dim testRange As Range, rr2Dest As Range, idCella As Range
Dim alastRow2 As Long, resultM As Long
Set testWS = Workbooks("Test.xlsx").Worksheets("October")                                       'set the 2nd workbook as testWS
Set testRange = testWS.Columns(1)                                                               'searching only column A on testWS (2nd workbook)
alastRow2 = Worksheets("Reruns To Pull").Cells(Rows.Count, "A").End(xlUp).Row                     'find last row in column A that has data on current workbook

For Each idCella In Worksheets("Reruns To Pull").Range("A1:A" & alastRow2).Cells                'for each cell in Column A on current workbook (eventually I want to loop through Column A, D, G, J.  All will be variable ranges)
If idCella.Borders.Color = RGB(0, 0, 192) Then                                                  'On current workbook, if cells in Col A borders.color = blue then
resultM = Application.Match(idCella.Value, testRange, 0)                                        'find exact match on Test.xlsx (2nd workbook) and store in variable resultM
                          'look up value is the first cell found on current workbook that has blue border
                          'the range I want to search is column A of Test.xlsx
Set rr2Dest.Value = resultM                                                                        'trying to set this result to a variable so I can offset the range location by 1 column (Result from current workbook goes to Column B on Tets.xlsx workbook)
rr2Dest.Value = idCella.Value
rr2Dest.Interior.Color = idCella.Interior.Color                                                 'everything I want to transfer into Column B on the 2nd workbook
rr2Dest.Borders.Color = idCella.Borders.Color
rr2Dest.Borders.Weight = idCella.Borders.Weight

End If

Next idCella
End Sub```

Answer 1

从rr2Dest开始，使用.End(xlToRight).Column获取下一个空闲单元格，然后更新该单元格的值(以及颜色、权重等(。

https://learn.microsoft.com/en-us/office/vba/api/excel.range.end

Answer 2

Set rr2dest = testWS.Range("A" & CStr(resultM)).Offset(0, 1)

解决了问题。我没有意识到宏没有跟踪它所在的工作簿。所以有一次我指定了它，并将CStr与包含匹配项的变量结合使用。

只要把舒特的答案加进去就可以了。