VBA for find and replace loop

[butters149]

Hey everyone,

My first post here and I am also fairly new to VBA but I've been having some minor success with just trial and error. My most frustrating struggle so far is the loop function. Below is the function I am trying to do. Basically I want to search for Collector, move to the cell next to it which is blank and add a #. I have to do this because my other vba macro will stop selection if it see a blank cell. I want to find all Collector and then replace the blank cell next to it with a #.

Sub Macro20()
'
' Macro20 Macro
'
' Keyboard Shortcut: Ctrl+g
'
Do
Cells.Find(What:="Collector", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "#"
If Not foundCell Is Nothing Then
Exit Do
Loop


End Sub

 

[My Aswer Is This]

What column will we be looking in for "Collector"
Tell me column A or B or something like that

 

[butters149]

Column B, and then I will switch to Column A with the offset.

 

[My Aswer Is This]

Assuming we will be search column A try this:

If some other column Change the A you see in 3 places to the proper column.

Sub Test()
'Modified 3-21-18 7:40 PM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, "A").Value = "Collector" Then Cells(i, "A").Offset(, 1).Value = "#"
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Well then try this searching column B

Sub Test()
'Modified 3-21-18 7:40 PM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, "B").Value = "Collector" Then Cells(i, "B").Offset(, -1).Value = "#"
    Next
Application.ScreenUpdating = True
End Sub

 

[butters149]

Thank-you! This worked =)

Assuming we will be search column A try this:

If some other column Change the A you see in 3 places to the proper column.

Sub Test()
'Modified 3-21-18 7:40 PM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, "A").Value = "Collector" Then Cells(i, "A").Offset(, 1).Value = "#"
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Thank-you! This worked =)

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.