VBA Find and Replace

[Simba75]

Hi, I hope somebody can help with this issue that im struggling to solve.

I have a list of data in sheet1, I need to find text in a column within "Sheet1" and replace it with text that is in a range located in sheet2.

For example, the range is C5:D34 (sheet2), i need to find what is in C5 of sheet2 within the list in Sheet1 and replace the text with D5 of sheet2. Repeat for each row of the range of sheet2 unless the row is blank.

I hope this explains basically what I need to do and thanks in advance for any help received.

 

[JLGWhiz]

Hi Simba75, Welcome to MrExcel Forum and Message Board.
It would help to narrow the range on sheet 1 a bit but let's give it a try with what you have in the post.

Sub swap()
Dim c As Range, fRng As Range
For Each c In Sheets(2).Range("C5:C34")
    Set fRng = Sheets(1).UsedRange.Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
    fRng = WorksheetFunction.Replace(fRng.Value, 1, Len(c.Value), c.Offset(0, 1).Value)
Next
End Sub

 

[Simba75]

Many thanks JLGWhiz, from your post I managed to get things working
Here is the code that I used:

Sub swap()
Dim c As Range, fRng As Range
For Each c In Sheets("Holiday Entry").Range("L39:L44")
    Set fRng = Sheets("Holiday Database").Range("K3:K50000").Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
    fRng = WorksheetFunction.Replace(fRng.Value, 1, Len(c.Value), c.Offset(0, 1).Value)
NextEnd 
Sub

To expand on this, how would you suggest to do the following

Find value in the "Holiday Database" range and replace with alternative text (as already achieved in above code)
Additionally, also replace contents in ("Holiday Database" column M) with copied text from ("Holiday Entry" column N)

Once again, thanks for your input so far.

Regards

 

[JLGWhiz]

Many thanks JLGWhiz, from your post I managed to get things working
Here is the code that I used:

Sub swap()
Dim c As Range, fRng As Range
For Each c In Sheets("Holiday Entry").Range("L39:L44")
    Set fRng = Sheets("Holiday Database").Range("K3:K50000").Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
    fRng = WorksheetFunction.Replace(fRng.Value, 1, Len(c.Value), c.Offset(0, 1).Value)
NextEnd 
Sub

To expand on this, how would you suggest to do the following

Find value in the "Holiday Database" range and replace with alternative text (as already achieved in above code)
Additionally, also replace contents in ("Holiday Database" column M) with copied text from ("Holiday Entry" column N)

Once again, thanks for your input so far.

Regards

It is the same code, just add another loop with the different parameters.
Regards, JLG

 

[Simba75]

It is the same code, just add another loop with the different parameters.
Regards, JLG

Hi JLG,

Yes that is what I thought but while the original code finds and replaces unique text, the additional request is data that is entered numerous times, therefore the code has to find the unique text first as in the original code and then use that as a reference to paste a new value in "Holiday Database column M. I can't perform the same code on column M as the data is not unique?

Apologies if I have got this wrong but as you can probably tell, my vba knowledge is shockingly poor.

Thanks
Simba

 

[JLGWhiz]

I guess I am not clear on what you are trying to do. The code provided and modified by you will search in database for each item in cells L39:L44 of Entry, then if matched will replace the found value with a value from Entry column K same row as searched item. That part seems to be accomplished. Now the question is, do you want column M value of Database, on the same row as the found item in Database column L, replaced with a value from column N of Entry on the same row as the searched item? Or are you asking to do an entirely different search? If it is the first scenario, then this code would work:

Sub swap()
Dim c As Range, fRng As Range
For Each c In Sheets("Holiday Entry").Range("L39:L44")
    Set fRng = Sheets("Holiday Database").Range("K3:K50000").Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
    fRng = WorksheetFunction.Replace(fRng.Value, 1, Len(c.Value), c.Offset(0, 1).Value)
    fRng.Offset(0, 2) = WorksheetFunction(fRng.Offset(0, 2).Value, 1, _
    Len(fRng.Offset(0, 2).Value), c.Offset(0, 2).Value)
Next
End Sub

 

[Simba75]

Hi again Whiz

I will try to be more clear on the requirement:
Find text that is in column L "Entry" in Column K "Database"
Replace found text in Column K "Database" with text from Column M "Entry" (so far your first code does this)
In the same line as the found text in column K "Database" copy text from column N "Entry" into column M of "Database"

Hmm, I think thats clearer (maybe)

I ran your last suggestion and it errored with the following message:
Runtime error "438"
Object doesn't support this property or method.

Again, thanks for your continued help.

 

[JLGWhiz]

Hi again Whiz

I will try to be more clear on the requirement:
Find text that is in column L "Entry" in Column K "Database"
Replace found text in Column K "Database" with text from Column M "Entry" (so far your first code does this)
In the same line as the found text in column K "Database" copy text from column N "Entry" into column M of "Database"

Hmm, I think thats clearer (maybe)

I ran your last suggestion and it errored with the following message:
Runtime error "438"
Object doesn't support this property or method.

Again, thanks for your continued help.

You would think that some simple little thing like forgetting to put the function name in the code wouldn't make so much difference, but it does! I left our "Replace" in the added code line. Try it now.

Sub swap2()
Dim c As Range, fRng As Range
For Each c In Sheets(1).Range("L39:L44")
    Set fRng = Sheets(2).Range("K3:K50000").Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
    fRng = WorksheetFunction.Replace(fRng.Value, 1, Len(c.Value), c.Offset(0, 1).Value)
    fRng.Offset(0, 2) = WorksheetFunction.Replace(fRng.Offset(0, 2).Value, 1, _
    Len(fRng.Offset(0, 2).Value), c.Offset(0, 2).Value)
Next
End Sub