I need to replace the value in column H with value from a cell in column U when criterias are met
here are the criterias:
If column O contains keywords "P-" and "MANHOLE"
and if column F contains exactly "P1"
then the value in column H in the rows that meet criterias above, gets replaced by the value in
column U on the row where column S contains "TYPE", "P", "MANHOLE". code below is not working, the values are not getting replaced
I feel the culprit is the code below, I am not using this correctly
example file below
here are the criterias:
If column O contains keywords "P-" and "MANHOLE"
and if column F contains exactly "P1"
then the value in column H in the rows that meet criterias above, gets replaced by the value in
column U on the row where column S contains "TYPE", "P", "MANHOLE". code below is not working, the values are not getting replaced
VBA Code:
Sub ChangeValue()
Dim i As Long, lastRow As Long
lastRow = ws1.Cells(ws1.Rows.Count, "O").End(xlUp).Row
For i = 2 To lastRow
If ws1.Range("O" & i).Value Like "*P-*" And _
ws1.Range("O" & i).Value Like "*MANHOLE*" And _
ws1.Range("F" & i).Value Like "P1" And _
StrComp("TYPE", Left(ws1.Range("S" & i).Value, 4), vbTextCompare) = 0 And _
StrComp("P", Mid(ws1.Range("S" & i).Value, 5, 1), vbTextCompare) = 0 And _
StrComp("MANHOLE", Right(ws1.Range("S" & i).Value, 7), vbTextCompare) = 0 Then
ws1.Range("H" & i).Value = ws1.Range("U" & i).Value
End If
Next i
End Sub
I feel the culprit is the code below, I am not using this correctly
VBA Code:
StrComp("TYPE", Left(ws1.Range("S" & i).Value, 4), vbTextCompare) = 0 And _
StrComp("P", Mid(ws1.Range("S" & i).Value, 5, 1), vbTextCompare) = 0 And _
StrComp("MANHOLE", Right(ws1.Range("S" & i).Value, 7), vbTextCompare) = 0 Then
example file below