excel - 如何将单元格与列进行比较,如果匹配替换行?
问题描述
我正在尝试匹配两个工作表中的两列。如果它们匹配,我希望表 1 中的行替换表 2 中的行。
我接近了,但现在我需要覆盖这一行。
我尝试了 selection.paste 但没有奏效。
我试过这个:
Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Sheets("Mutatie overzicht bezetting").Range("B5:AC5").Select
Selection.Copy
Sheets("BEZETTING 2020").Activate
With ActiveSheet
.Select
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
If .Value = Sheets("Mutatie overzicht bezetting").Range("C5") Then .EntireRow.Select
End If
End With
Next Lrow
End With
End Sub
解决方案
正如 SJR 所说,您的语法在第三行是很差的。此外,您缺少工作表引用,使您的代码相当混乱。请参阅下面的代码以更接近您的需要:
Sub LoopThroughCities()
Dim LstRw As Long, ThsRw As Long, ThsEMPLOYEE As String
With Sheets("Bezetting 2020")
LstRw = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
ThsEMPLOYEE = InputBox("Which employee do you want to search for?")
If Len(ThsEMPLOYEE) = 0 Then Exit Sub
For ThsRw = 2 To LstRw
With Sheets("Sheettocopyfrom")
If .Cells(ThsRw, 5).Value = ThsEMPLOYEE Then .Cells(ThsRw, 22).Resize(, 3).Copy Sheets("Sheettocopyto").Cells(ThsRw, 22)
End With
Next
End Sub