I have two sheets – Latency, TP. I need to copy col M from "Latency" and paste it into col D of "TP" only if "Latency" col E has the string “COMPATIBLE” and col O has the string “Pass”.
我有两张纸 - Latency,TP。我需要从“Latency”复制col M并将其粘贴到“TP”的col D中,只要“Latency”col E具有字符串“COMPATIBLE”并且col O具有字符串“Pass”。
I have the below code, but it doesn't give any result.
我有以下代码,但它没有给出任何结果。
I'm not sure whats wrong with it:
我不确定它有什么问题:
Sub sbMoveData()
Dim lRow As Integer, i As Integer, j As Integer
'Find last roe in Sheet1
With Worksheets("Latency")
lRow = .Cells.SpecialCells(xlLastCell).Row
j = 1
For i = 1 To lRow
If UCase(.Range("E" & i)) = "COMPATIBLE" And UCase(.Range("O" & i)) = "Pass" Then
.Range("M" & i).Copy Destination:=Worksheets("TP").Range("D" & j)
j = j + 1
End If
Next
End With
End Sub
3 个解决方案
#1
0
Try this
Sub sbMoveData()
Dim lRow As Integer, i As Integer, j As Integer
Dim ws1, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Latency")
Set ws2 = ThisWorkbook.Sheets("TP")
'Find last roe in Sheet1
lRow = ws1.Cells.SpecialCells(xlLastCell).Row
j = 1
For i = 1 To lRow
If ws1.Range("A" & i) = "COMPATIBLE" And ws1.Range("B" & i) = "Pass" Then
ws1.Range("M" & i).Copy Destination:=ws2.Range("D" & j)
j = j + 1
End If
Next i
End Sub
#2
2
UCase(.Range("O" & i)) = "Pass" Will always be false :-)
UCase(.Range(“O”&i))=“通过”将永远是假的:-)
#3
1
You are never going to match UCase(Cell) = "Pass", right? You either need to have:
你永远不会匹配UCase(Cell)=“Pass”,对吧?你要么需要:
UCase(.Range("O" & i)) = "PASS"
or
.Range("O" & i) = "Pass"
#1
0
Try this
Sub sbMoveData()
Dim lRow As Integer, i As Integer, j As Integer
Dim ws1, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Latency")
Set ws2 = ThisWorkbook.Sheets("TP")
'Find last roe in Sheet1
lRow = ws1.Cells.SpecialCells(xlLastCell).Row
j = 1
For i = 1 To lRow
If ws1.Range("A" & i) = "COMPATIBLE" And ws1.Range("B" & i) = "Pass" Then
ws1.Range("M" & i).Copy Destination:=ws2.Range("D" & j)
j = j + 1
End If
Next i
End Sub
#2
2
UCase(.Range("O" & i)) = "Pass" Will always be false :-)
UCase(.Range(“O”&i))=“通过”将永远是假的:-)
#3
1
You are never going to match UCase(Cell) = "Pass", right? You either need to have:
你永远不会匹配UCase(Cell)=“Pass”,对吧?你要么需要:
UCase(.Range("O" & i)) = "PASS"
or
.Range("O" & i) = "Pass"