Find and replace with a specified cell in the same row

[jb9901]

I want to search my whole sheet for the value "-1" and where it is found, I want to replace it with the value in the last cell of its row. In the example in the attached image, I would want the "-1" at row 4 of column HP to be replaced with the last cell in its row (column HX, row 4, value "1"). I have many instances of "-1" in the sheet, and I want these all replaced with the value at the end of the row where the "-1" is found.

 

[Joe4]

Welcome to the Board!

Do you really want to search every column on the whole sheet, or just certain ones?
If just certain ones, which ones?

I am pretty sure that this will require a VBA solution (if you do want to do each one manually).
Are you good with that?

 

[jb9901]

Thanks for the reply!

Yes, I will need to search nearly every column in the sheet (in my sheet, columns I -> HW). I don't have experience with VBA solutions, but hopefully I can implement it if someone can come up with it.

 

[Joe4]

Is that last value in a row ALWAYS going to be in column HX?
What if the last value in the row is also -1 (if that is even possible)?

 

[Joe4]

See if this VBA code does what you want:

Sub MyReplaceAll()

    Dim cell As Range
    Dim x As Double
    
    Application.ScreenUpdating = False
    
    On Error GoTo err_complete

'   Loop until all values are replaced
    Do
'       Get address of instance of -1
        Set cell = Columns("I:HW").Find(What:="-1", After:=Range("I1"), LookIn:=xlFormulas2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
'       Get last value in row
        x = Cells(cell.Row, Columns.Count).End(xlToLeft).Value
'       Change value in cell -1
        If x <> -1 Then
            cell.Value = x
        Else
            cell.Value = 99999
        End If
    Loop
    
    
err_complete:
    Application.ScreenUpdating = True
    If Err.Number = 91 Then
        MsgBox "Process complete, all values of -1 in columns I:HW have been replaced"
    Else
        MsgBox Err.Number & Err.Description
    End If

End Sub

 

[jb9901]

Is that last value in a row ALWAYS going to be in column HX?
What if the last value in the row is also -1 (if that is even possible)?

Yes that last value in a row will always be in column HX.

If there is a rare case where the last row is -1, that's fine to replace with -1.

 

[Joe4]

If there is a rare case where the last row is -1, that's fine to replace with -1.

Hmmm... That makes it a lot harder, as it keeps wanting to loop on that.
Any chance we can replace it to something else?

 

[Joe4]

If HX is -1, and we can replace any other -1s in that row with another value, like say "999" (note that this can be any value other than -1), this code would work:

Sub MyReplaceAll()

    Dim cell As Range
    Dim x As Double
    
    Application.ScreenUpdating = False
    
    On Error GoTo err_complete

'   Loop until all values are replaced
    Do
'       Get address of instance of -1
        Set cell = Columns("I:HW").Find(What:="-1", After:=Range("I1"), LookIn:=xlFormulas2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
'       Replace with value from HX value in that row
        x = Cells(cell.Row, "HX").Value
        If x <> -1 Then
            cell.Value = x
        Else
            cell.Value = 999
        End If
    Loop
    
    
err_complete:
'   Exit if no more -1 values found
    Application.ScreenUpdating = True
    If Err.Number = 91 Then
        MsgBox "Process complete, all values of -1 in columns I:HW have been replaced"
    Else
        MsgBox Err.Number & Err.Description
    End If

End Sub

 

[jb9901]

Hmmm... That makes it a lot harder, as it keeps wanting to loop on that.
Any chance we can replace it to something else?

Yes, if the last cell in the row is -1, we could replace instances of -1 in that row to "NA"

 

[Joe4]

Yes, if the last cell in the row is -1, we could replace instances of -1 in that row to "NA"

Great, then this should work:

Sub MyReplaceAll()

    Dim cell As Range
    Dim x As Double
    
    Application.ScreenUpdating = False
    
    On Error GoTo err_complete

'   Loop until all values are replaced
    Do
'       Get address of instance of -1
        Set cell = Columns("I:HW").Find(What:="-1", After:=Range("I1"), LookIn:=xlFormulas2, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
'       Replace with value from HX value in that row
        x = Cells(cell.Row, "HX").Value
        If x <> -1 Then
            cell.Value = x
        Else
            cell.Value = "NA"
        End If
    Loop
    
    
err_complete:
'   Exit if no more -1 values found
    Application.ScreenUpdating = True
    If Err.Number = 91 Then
        MsgBox "Process complete, all values of -1 in columns I:HW have been replaced"
    Else
        MsgBox Err.Number & Err.Description
    End If

End Sub