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

zack8576

Active Member
Joined
Dec 27, 2021
Messages
271
Office Version
  1. 365
Platform
  1. Windows
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

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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try the following:

VBA Code:
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:

VBA Code:
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.
 
Last edited:
Upvote 0
Solution
Try the following:

VBA Code:
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:

VBA Code:
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 !
 
Upvote 1

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top