Find & Replace macro

[Sun13Banjo]

Hi All. This is my first post here, but I have been browsing your forum for a while now and have found it a very useful resource, so thanks for your (unknowing) help so far.

I have a problem where I want to search column K in a spreadsheet, for a value in cell B2 on a second spreadsheet, and replace it with the value in C2 of the second spreadsheet. I then want the macro to move down to B3 in the second spreadsheet, but replace it with C3, and so on until all values in the second spreadsheet have been found/replaced.

So far I have the code to find and replace C2.s value with C3's, but I'm not sure how to make this move down to run therough the other values.

Any help would be greatly appreciated.

Code so far (only for working on one spreadsheet so far):

Sub MassReplace()

Range("K:K").Select
   Selection.Replace what:=Range("B2").Value, _
       replacement:=Range("C2").Value, _
       LookAt:=xlPart, _
       SearchOrder:=xlByRows, _
       MatchCase:=False
       
End Sub

Thanks

Sun 13

[GaidenFocus]

A simple loop or with function would help you.

do until activecell.value = ""

replace code

activecell.offset(1,0).select

loop

[Sun13Banjo]

Thanks for your reply. The loop doesn't work as the B2/C2 references are specific, and I need the Find and Replace to move down to B3/C3 and so on until the active cell is blank

Sun 13

[GaidenFocus]

send a sample sheet and ill set it up

[Sun13Banjo]

Thanks GaidenFocus. I can't send any files from work

I will upload from home later.

Thanksfor trying tho - there's probably a really simple solution that I'm missing

[GaidenFocus]

oh i got it haha

are you familiar with range variables?

dim tmpbox as range


Sub MassReplace()
set tmpbox as range("b2")

do until tmpbox.value = ""
Range("K:K").Select
   Selection.Replace what:=tmpbox, _
       replacement:=tmpbox.offset(0,1), _
       LookAt:=xlPart, _
       SearchOrder:=xlByRows, _
       MatchCase:=False

set tmpbox = tmpbox.offset(1,0)
loop     
 
End Sub

[Sun13Banjo]

The quick answer would be no

I have tried your code and am getting a compile error:syntax error on the line 'set tmpbox as range("b2")'

[GaidenFocus]

sorry change as to =

[GaidenFocus]

dim tmpbox as range


Sub MassReplace()
set tmpbox = range("b2")

do until tmpbox.value = ""
Range("K:K").Select
   Selection.Replace what:=tmpbox, _
       replacement:=tmpbox.offset(0,1), _
       LookAt:=xlPart, _
       SearchOrder:=xlByRows, _
       MatchCase:=False

set tmpbox = tmpbox.offset(1,0)
loop     
 
End Sub

[Sun13Banjo]

On second thoughts it works perfectly!

Many, many thanks for this. I may well be carried shoulder high by my colleagues in honour of my having saved them hours of work.

I will, of course, take all the credit, but deep down we'll all know it was you!

Thanks again

[Sun13Banjo]

Thanks again GaidenFocus.

The code is not actually replacing any text, altho for the life of me I can't figure out why

[GaidenFocus]

Haha, no worries, I'm sure everyone here has done that at some point. I've actually gotten a raise from some of the things these guys helped me with. Just give me some rep! I'm negative for some reason. Take care.

[Sun13Banjo]

Rep given, and well deserved!