我在下面的代码上遇到了麻烦。
我正在尝试使用wb2中“ A”列中的值来搜索wb1中“ G”列中的值。
wb2中的“ A”列包含一长串数字,我正尝试在wb1的“ G”列中搜索该数字的精确匹配项。
当存在匹配项时,我需要将wb2中正确行上的“ AF”列的值设置为与wb1中相应的匹配项,但从另一列开始,可能是“ Z”列而不是“ G”列。
运行宏时,to工作簿已经打开。
希望你能帮帮我。
提前致谢。
Sub ROAC()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim y As Integer
Dim sht As Worksheet
Set wb1 = Workbooks("EP_BB_DK_ny.xlsm")
Set wb2 = Workbooks("Laaneoversigt.xlsm")
Set sht = wb2.Worksheets("oversigt")
LastRow = sht.Cells(sht.Rows.Count, "AF").End(xlUp).Row
LastRowWb1 = wb1.Sheets("Period").Range(wb1.Sheets("Period").Range("G1"), wb1.Sheets("Period").Range("G1").End(xlDown)).Rows.Count
LastRowWb2 = wb2.Sheets("Oversigt").Range(wb2.Sheets("Oversigt").Range("A1"), wb2.Sheets("Oversigt").Range("A1").End(xlDown)).Rows.Count
For y = 7 To LastRowWb1
For x = 1 To LastRowWb2
If wb1.Sheets("Period").Range("G" & y).Value = wb2.Sheets("Oversigt").Range("A" & x).Value Then
wb2.Sheets("Oversigt").Range("AF" & LastRow).Offset(1, 0).Value = wb1.Sheets("Period").Range("G" & y)
End If
Next x
Next y
End Sub
这就是我如何执行您的要求(假设我仍然足够清楚地理解它!)。此代码循环遍历wb2的A列中的所有行,并对wb1的G列执行“查找”操作。找到它的地方,它将该行的wb2中的AF列设置为同一行中wb1的Z列中的值。
Sub ROAC()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim y As Integer
Dim sht As Worksheet
Set wb1 = Workbooks("EP_BB_DK_ny.xlsm")
Set wb2 = Workbooks("Laaneoversigt.xlsm")
Set wb1sht = wb1.Worksheets("Period")
Set wb2sht = wb2.Worksheets("oversigt")
LastRowWb1 = wb1sht.Cells(wb1sht.Rows.Count, "G").End(xlUp).Row
LastRowWb2 = wb2sht.Cells(wb2sht.Rows.Count, "A").End(xlUp).Row
For y = 1 To LastRowWb2
findMe = wb2sht.Range("A" & y).Value
With wb1sht.Range("G7:G" & LastRowWb1)
Set oFound = .Find(findMe)
If Not oFound Is Nothing Then
' Found number - set AF in wb2 on this row to Z on the same row from wb1
wb2sht.Range("AF" & oFound.Row).Value = wb1sht.Range("Z" & oFound.Row).Value
Else
' Didn't find number, so do whatever you might need to do to handle this in here...
End If
End With
Next
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句