Looping to find and replace a list of values

cacahuatitaCH

New Member
Joined
Jan 8, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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?
 
Solution

cacahuatitaCH

New Member
Joined
Jan 8, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Ok, this will do both
VBA Code:
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

New Member
Joined
Jan 8, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,144,339
Messages
5,723,800
Members
422,517
Latest member
VisioExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top