Looping to find and replace a list of values

[cacahuatitaCH]

Hello everyone!

First post, so sorry if I mess up a bit, full disclosure, I'm a pretty basic VBA user.

I have this worksheet with user entered data in several areas. It is basically a long format for people to record results of tests.

I am working on a CLEAR DATA macro here, so that all user entered data fields can be. I want to keep this as flexible as possible, as changes are still being requested on this and so I can recycle the code on other files as needed.

Basically, this is looking for the field name, then changes the adjacent cell's value, which is the user entered data.

My current code is attached below, what I want to change is, I want the search to loop so it can change all the applicable user entered data instances. And ideally, the Defaultee variable would be a series of strings that I need to look for.

Sub Defaultees()

Dim Defaultee As String
    Defaultee = "Requisition Date"

Dim FoundCell As Range

Set FoundCell = Cells.Find(What:=Defaultee, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
       
    If Not FoundCell Is Nothing Then
                Dim DefaulteeRow As Long
                DefaulteeRow = FoundCell.Row
                    Dim DefaulteeColumn As String
                    'Number' DefaulteeColumn = FoundCell.Column
                    DefaulteeColumn = Chr(FoundCell.Column + 65)
                'Dim DefaulteeAddress As Range
                Dim DefaulteeRange As String
                    DefaulteeRange = DefaulteeColumn & DefaulteeRow
                Range(DefaulteeRange).Value = "DD/MMM/YYYY"
End If

MsgBox "DONE!"

End Sub

I have a sample file prepared, but couldn't find how to attach it.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Defaultees()

   With ActiveSheet.UsedRange
      .Replace "Requisition Date", "=Requisition Date", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).Offset(, 1).Value = "DD/MM/YYYY"
      .Replace "=Requisition Date", "Requisition Date", xlWhole, , False, , False, False
   End With

MsgBox "DONE!"

End Sub

What are some of the other values to find & what should be entered into the neighbouring cell?

 

[cacahuatitaCH]

Hi & welcome to MrExcel.
How about

Sub Defaultees()

   With ActiveSheet.UsedRange
      .Replace "Requisition Date", "=Requisition Date", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).Offset(, 1).Value = "DD/MM/YYYY"
      .Replace "=Requisition Date", "Requisition Date", xlWhole, , False, , False, False
   End With

MsgBox "DONE!"

End Sub

What are some of the other values to find & what should be entered into the neighbouring cell?

Thanks for your reply, it looks much more neat that way!

I have an "Executed by" field, the neighboring cell needs to be "NAME"

 

[Fluff]

Ok, this will do both

Sub Defaultees()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Requisition Date", "DD/MM/YYYY", "Executed by", "NAME")
   With ActiveSheet.UsedRange
      For i = 0 To UBound(Ary) Step 2
         .Replace Ary(i), "=" & Ary(i), xlWhole, , False, , False, False
         .SpecialCells(xlFormulas, xlErrors).Offset(, 1).Value = Ary(i + 1)
         .Replace "=" & Ary(i), Ary(i), xlWhole, , False, , False, False
      Next i
   End With
   MsgBox "DONE!"
End Sub

 

[cacahuatitaCH]

Thank you soooo much, I will keep your code in mind when I do other changes.

You've been very helpful

Ok, this will do both

Sub Defaultees()
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Array("Requisition Date", "DD/MM/YYYY", "Executed by", "NAME")
   With ActiveSheet.UsedRange
      For i = 0 To UBound(Ary) Step 2
         .Replace Ary(i), "=" & Ary(i), xlWhole, , False, , False, False
         .SpecialCells(xlFormulas, xlErrors).Offset(, 1).Value = Ary(i + 1)
         .Replace "=" & Ary(i), Ary(i), xlWhole, , False, , False, False
      Next i
   End With
   MsgBox "DONE!"
End Sub

 

[Fluff]

You're welcome & thanks for the feedback.