如果Sheet2.Column“ A”中的Cell.value在Sheet(“ Civil”)。Column“ A”中不匹配,则将该单元格复制到Sheets(“ Sheet2).Column” D“中
正确的结果看起来像在所附图片上,但是我在编写正确的代码以填充Sheets(“ Sheet2).Column” D“时遇到问题
Sub NewSearch_A()
Dim cell As Range, rng As Range, rng2 As Range, rng3 As Range, cell1 As Range, n As Integer, m As Integer
Set rng = Sheets("Civil").Range("A2:A1000")
Set rng2 = Sheets("Sheet2").Range("A1:A100")
Set rng3 = Sheets("Sheet2").Range("C1:C100")
Set rng4 = Sheets("Sheet2").Range("D1:D100")
n = 1
m = 1
For Each cell In rng
n = n + 1
For Each cell1 In rng2
m = m + 1
If cell.Value = cell1.Value Then
Sheets("Sheet2").Range("C" & m & ":C" & m).Value = Sheets("Civil").Range("B" & n & ":B" & n).Value
Else
' ????????????????????????????????????????????????
End If
Next cell1
m = 1
Next cell
ActiveSheet.Columns("A:C").AutoFit
End Sub
使用WorksheetFunction MATCH函数避免第二个循环。
Sub NewSearch_A()
Dim rw As Long, mtch As Variant, wsc As Worksheet
Set wsc = Worksheets("Civil")
With Worksheets("Sheet2")
For rw = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
mtch = Application.Match(.Cells(rw, "A").Value2, wsc.Columns("A"), 0)
If IsError(mtch) Then
.Cells(rw, "D") = .Cells(rw, "A").Value2
Else
.Cells(rw, "C") = wsc.Cells(mtch, "B").Value2
End If
Next rw
End With
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句