replace value in a cell with a value from a different cell when criterias are met

[zack8576]

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

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

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

2022100098.csv

Shared with Dropbox

www.dropbox.com

 

[DanteAmor]

Try the following:

Sub ChangeValue()
  Dim i As Long, lastRow As Long
 
  'If the codename of your sheet is ws, then skip this part
  Dim ws1 As Worksheet
  Set ws1 = Sheets("Sheet1")
 
  lastRow = ws1.Cells(ws1.Rows.Count, "O").End(xlUp).Row
  For i = 2 To lastRow
    If UCase(ws1.Range("O" & i).Value) Like "*P-*" And _
       UCase(ws1.Range("O" & i).Value) Like "*MANHOLE*" And _
       UCase(ws1.Range("F" & i).Value) = "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

If in cell "S" you have something like this: "TYPEP?MANHOLE" then try this:

Sub ChangeValue_2()
  Dim i As Long, lastRow As Long
 
  'If the codename of your sheet is ws, then skip this part
  Dim ws1 As Worksheet
  Set ws1 = Sheets("Sheet1")
 
  lastRow = ws1.Cells(ws1.Rows.Count, "O").End(xlUp).Row
  For i = 2 To lastRow
    If UCase(ws1.Range("O" & i).Value) Like "*P-*" And _
       UCase(ws1.Range("O" & i).Value) Like "*MANHOLE*" And _
       UCase(ws1.Range("F" & i).Value) = "P1" And _
       UCase(ws1.Range("S" & i).Value) Like "TYPEP?MANHOLE" Then
         ws1.Range("H" & i).Value = ws1.Range("U" & i).Value
    End If
  Next i
End Sub

If it doesn't work, you could put examples with XL2BB tool to see some records that match the criteria.

In your example file there is no data in column S, Also has no data in column U Then you could provide other examples.

 

[zack8576]

Try the following:

Sub ChangeValue()
  Dim i As Long, lastRow As Long
 
  'If the codename of your sheet is ws, then skip this part
  Dim ws1 As Worksheet
  Set ws1 = Sheets("Sheet1")
 
  lastRow = ws1.Cells(ws1.Rows.Count, "O").End(xlUp).Row
  For i = 2 To lastRow
    If UCase(ws1.Range("O" & i).Value) Like "*P-*" And _
       UCase(ws1.Range("O" & i).Value) Like "*MANHOLE*" And _
       UCase(ws1.Range("F" & i).Value) = "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

If in cell "S" you have something like this: "TYPEP?MANHOLE" then try this:

Sub ChangeValue_2()
  Dim i As Long, lastRow As Long
 
  'If the codename of your sheet is ws, then skip this part
  Dim ws1 As Worksheet
  Set ws1 = Sheets("Sheet1")
 
  lastRow = ws1.Cells(ws1.Rows.Count, "O").End(xlUp).Row
  For i = 2 To lastRow
    If UCase(ws1.Range("O" & i).Value) Like "*P-*" And _
       UCase(ws1.Range("O" & i).Value) Like "*MANHOLE*" And _
       UCase(ws1.Range("F" & i).Value) = "P1" And _
       UCase(ws1.Range("S" & i).Value) Like "TYPEP?MANHOLE" Then
         ws1.Range("H" & i).Value = ws1.Range("U" & i).Value
    End If
  Next i
End Sub

If it doesn't work, you could put examples with XL2BB tool to see some records that match the criteria.

In your example file there is no data in column S, Also has no data in column U Then you could provide other examples.

thanks Dante, it worked. appreciate your help !