How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask John D Your Own Question
John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

Excel Macro Help Required How do I get the following Macro

This answer was rated:

Excel Macro Help Required

How do I get the following Macro shown at the end to repeat but on repeat the

"Repl = Sheets("Data").Range("A1").CurrentRegion.Value"
would be
"Repl = Sheets("Data").Range("B1").CurrentRegion.Value
Repl = Sheets("Data").Range("C1").CurrentRegion.Value

Sub Replace_Values()
Dim Repl
Dim i As Long

Repl = Sheets("Data").Range("A1").CurrentRegion.Value
With Sheets("Sheet1").Range("A1:G1000")
For i = 1 To UBound(Repl, 1)
.Replace What:=Repl(i, 1), Replacement:=Repl(i, 2), _
LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False, _
Next i
End With
End Sub



Could you explain in words what you want the code to do





Customer: replied 5 years ago.
Hi John

In worksheet "Sheet1" I have a list of random data from "A1:G1000"
Example: A1=Apples, B1=Oranges, C1=Apples, D1=Pears, E1=Pears random.

In worksheet "Sheet2" I have a list of the items that can be found in "Sheet1" so A1=Apples, A2=Oranges, A3=Pears.

I would like the Macro to look up Cell A1 in Sheet2, "Apples" find all occurrences of "Apples" in Sheet1 "A1:G1000" and replace all found occurrences with a blank cell.

Hopefully that makes my question a little more clear, sorry for the confusion.




Here is the complete macro for that



Sub Main()
Application.ScreenUpdating = False
Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("Sheet2")
Set Rng1 = sht1.Range("A1:G1000")
Set Rng2 = sht2.UsedRange
For Each c2 In Rng2
For Each c1 In Rng1
If c1.Value = c2.Value Then c1.Value = ""
Next c1
Next c2
End Sub



where Sheet1 is the sheet that has the range A1:G1000 and where the matching cells will be replaced with blanks


Hope this is ok. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the solution



John D and other Microsoft Office Specialists are ready to help you