VBA find cell value in Col A to replace cell value in Col F with 0

[ceytl]

I have been looking around but cant find a VBA script that will let me do the following:

In Column A, find value "GA01US", then on the same row but in Column F replace the value with 0

Any help would be appreciated!

 

[Joe4]

Will there be a single replacement, or could there be multiple rows to replace?

 

[Joe4]

For single replacement, you could do something like this:

Sub MyReplace()

    Dim v As String
    Dim r As Long

'   Enter value to find
    v = "GA01US"

'   If value not found, go to err_chk
    On Error GoTo err_chk
    
'   Find row number it appears on
    r = Columns("A:A").Find(What:=v, After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
    
    On Error GoTo 0
    
'   Update column F of that row to be 0
    Cells(r, "F") = 0
    
    Exit Sub
    
'   error_handling code
err_chk:
    If Err.Number = 91 Then
        MsgBox "The value " & v & " is not found in column A", vbOKOnly, "ALERT!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
        
End Sub

 

[ceytl]

For single replacement, you could do something like this:

Sub MyReplace()

    Dim v As String
    Dim r As Long

'   Enter value to find
    v = "GA01US"

'   If value not found, go to err_chk
    On Error GoTo err_chk
   
'   Find row number it appears on
    r = Columns("A:A").Find(What:=v, After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
   
    On Error GoTo 0
   
'   Update column F of that row to be 0
    Cells(r, "F") = 0
   
    Exit Sub
   
'   error_handling code
err_chk:
    If Err.Number = 91 Then
        MsgBox "The value " & v & " is not found in column A", vbOKOnly, "ALERT!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
       
End Sub

I ran the script and there's an error:

9:Subscript out of range

 

[Joe4]

I ran the script and there's an error:

9:Subscript out of range

If you hit debug, which line of code is highlighted?

Is column F, perhaps:
- hidden?
- protected?
- part of a merged cell?

 

[ceytl]

If you hit debug, which line of code is highlighted?

Is column F, perhaps:
- hidden?
- protected?
- part of a merged cell?

Yes, some cells in Column A nd F are merged cells.

I did try it in a blank worksheet and I got the same error.

 

[Joe4]

Try changing this part:
LookIn:=xlFormulas2
to
LookIn:=xlFormulas

Also, I would HIGHLY recommend getting rid of the merged cells! Merged cells cause a ton of issues with things such as VBA and sorting.
They are a HUGE pain, and most experienced programmers avoid them like the plague.

Note that if you are simply merging cells across individual rows, you can achieve the same visual experience without all the issues by using the "Center Across Selection" formatting instead.
See here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[ceytl]

Try changing this part:
LookIn:=xlFormulas2
to
LookIn:=xlFormulas

Also, I would HIGHLY recommend getting rid of the merged cells! Merged cells cause a ton of issues with things such as VBA and sorting.
They are a HUGE pain, and most experienced programmers avoid them like the plague.

Note that if you are simply merging cells across individual rows, you can achieve the same visual experience without all the issues by using the "Center Across Selection" formatting instead.
See here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Thank you so much!

Last question, if I wanted to add 2 more parts to the code, what would I need to do?

 

[Joe4]

Last question, if I wanted to add 2 more parts to the code, what would I need to do?

Do you mean search for two other values? I would probably create a loop to do that.
Here is one way:

Sub MyReplace()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows in column A
    For r = 1 To lr
'       Check value on entry
        If (Cells(r, "A") = "GA01US") Or (Cells(r, "A") = "value2") Or (Cells(r, "A") = "value3") Then
'           Clear column F if value found in column A
            Cells(r, "F") = 0
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

Just change "value2" and "value3" with the other values you want to look for.
The other advantage to this code is that it will find ALL instances (in case there are more than one).

 

[ceytl]

Do you mean search for two other values? I would probably create a loop to do that.
Here is one way:

Sub MyReplace()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows in column A
    For r = 1 To lr
'       Check value on entry
        If (Cells(r, "A") = "GA01US") Or (Cells(r, "A") = "value2") Or (Cells(r, "A") = "value3") Then
'           Clear column F if value found in column A
            Cells(r, "F") = 0
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Just change "value2" and "value3" with the other values you want to look for.
The other advantage to this code is that it will find ALL instances (in case there are more than one).

Works perfect! Thank you so much!