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.

 

[jb9901]

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

Thanks for this, though I have not yet been able to test it in my Excel for Mac. I figured out how to open the VBA editor, inserted a module in the sheet of interest, copied and pasted the above, and hit run. However, Excel just hangs and doesn't appear to be doing anything -- perhaps the problem is that the dataset is very large (>44 000 rows)?

 

[Joe4]

44,000 rows and 222 columns is a LOT of data to get through, and loops are notoriously slow in VBA. It may take a little while for it to run through them all.

These minor amendments may help speed up things a little.

Sub MyReplaceAll()

    Dim cell As Range
    Dim x As Double
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = 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.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
        Application.EnableEvents = 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]

I'm not sure if I'm implementing this incorrectly, but I left it running overnight and Excel was still unresponsive in the morning (and the replacements were not done). Is there maybe an alternative route to consider?

 

[Akuini]

@jb9901
Here's another option to try:

Sub jb9901_1()
Dim i As Long, j As Long, n As Long
Dim va, vb
Dim t As Double

t = Timer
n = Range("I:HW").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

va = Range("I2:W" & n)
vb = Range("HX2:HX" & n)

For i = 1 To UBound(va, 1)
    For j = 1 To UBound(va, 2)
        If va(i, j) = -1 Then
            If vb(i, 1) <> -1 Then
                va(i, j) = vb(i, 1)
            Else
                 va(i, j) = "NA"
            End If
        End If
    Next
Next

Range("I2").Resize(UBound(va, 1), UBound(va, 2)) = va
Debug.Print "It's done in:  " & Format(Timer - t, "0.00") & " seconds"
End Sub

 

[jb9901]

@jb9901
Here's another option to try:

Sub jb9901_1()
Dim i As Long, j As Long, n As Long
Dim va, vb
Dim t As Double

t = Timer
n = Range("I:HW").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

va = Range("I2:W" & n)
vb = Range("HX2:HX" & n)

For i = 1 To UBound(va, 1)
    For j = 1 To UBound(va, 2)
        If va(i, j) = -1 Then
            If vb(i, 1) <> -1 Then
                va(i, j) = vb(i, 1)
            Else
                 va(i, j) = "NA"
            End If
        End If
    Next
Next

Range("I2").Resize(UBound(va, 1), UBound(va, 2)) = va
Debug.Print "It's done in:  " & Format(Timer - t, "0.00") & " seconds"
End Sub

Thank you! This seems to have worked after making a change in the code to say "va=Range("I2:HW" & n)". I believe the 'H' was missing above. By the way, it took 6.57 seconds!

 

[Akuini]

Thank you! This seems to have worked after making a change in the code to say "va=Range("I2:HW" & n)". I believe the 'H' was missing above. By the way, it took 6.57 seconds!

Ah, you're right, it should be HW instead of W.
You're welcome, glad to help & thanks for the feedback.