修改VBA代码,实现以下需求, 1.F列值如果只有三个字字符串,则清空该单元格 2.将表1的B列值与表2的N列值匹配,如果匹配一致,则将表2的O列值返回给表1的E列 3.如果上一步匹配不成功,则将表1的F列的值与表2的J列值匹配,如果匹配一致,则将表2的k列值返回给表1的E列, 4.如果上面三步都匹配不成功,则将表1的D列值与表2的G列匹配,如果匹配一致,则将表2的H列值返回给表1的E列。 5.如果第一次匹配一致,后两次匹配不执行,如果第一次匹配不一致,第二次匹配一致,则第三次匹配不执行,
时间: 2023-12-01 10:02:08 浏览: 101
VBA(Excel)代码.doc
以下是修改后的VBA代码,注意要在正确的工作簿中运行代码:
```
Sub matchAndReturn()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRow1 As Long, lastRow2 As Long
Dim i As Long, j As Long
Dim matchFound As Boolean
Set ws1 = ThisWorkbook.Worksheets("表1")
Set ws2 = ThisWorkbook.Worksheets("表2")
'清空F列只有三个字符的单元格
lastRow1 = ws1.Cells(ws1.Rows.Count, "F").End(xlUp).Row
For i = 2 To lastRow1
If Len(ws1.Cells(i, "F")) = 3 Then
ws1.Cells(i, "F").ClearContents
End If
Next i
'匹配表1的B列和表2的N列,返回O列值到表1的E列
lastRow1 = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
lastRow2 = ws2.Cells(ws2.Rows.Count, "N").End(xlUp).Row
For i = 2 To lastRow1
matchFound = False
For j = 2 To lastRow2
If ws1.Cells(i, "B") = ws2.Cells(j, "N") Then
ws1.Cells(i, "E") = ws2.Cells(j, "O")
matchFound = True
Exit For
End If
Next j
If matchFound Then
Exit For
End If
Next i
'如果第一次匹配不成功,匹配表1的F列和表2的J列,返回K列值到表1的E列
If Not matchFound Then
lastRow1 = ws1.Cells(ws1.Rows.Count, "F").End(xlUp).Row
lastRow2 = ws2.Cells(ws2.Rows.Count, "J").End(xlUp).Row
For i = 2 To lastRow1
matchFound = False
For j = 2 To lastRow2
If ws1.Cells(i, "F") = ws2.Cells(j, "J") Then
ws1.Cells(i, "E") = ws2.Cells(j, "K")
matchFound = True
Exit For
End If
Next j
If matchFound Then
Exit For
End If
Next i
End If
'如果前两次匹配都不成功,匹配表1的D列和表2的G列,返回H列值到表1的E列
If Not matchFound Then
lastRow1 = ws1.Cells(ws1.Rows.Count, "D").End(xlUp).Row
lastRow2 = ws2.Cells(ws2.Rows.Count, "G").End(xlUp).Row
For i = 2 To lastRow1
For j = 2 To lastRow2
If ws1.Cells(i, "D") = ws2.Cells(j, "G") Then
ws1.Cells(i, "E") = ws2.Cells(j, "H")
Exit For
End If
Next j
Next i
End If
'如果第一次匹配一致,后两次匹配不执行
'如果第一次匹配不一致,第二次匹配一致,则第三次匹配不执行
End Sub
```
阅读全文