First off let me say thanks to anyone that can help me out here. I am
sure my question will be easily answered. I have searched this group
for over an over and haven't found a solution that works that well.
What I am having problem with is a Find & Replace macro updating about
a 1000 corresponding ID's and replacing those numbers with
corresponding names.

I have an Excel document where my data is in column J only that I want
to change. I can't limit the find&replace macro to just that column (I
just don't know code that well).

I have a sheet called "data" that looks like

Column A (ID) Column B (NAME)

25743 Monster House
12790 Cars
54689 Saving Private Ryan

I then have the other sheet that has a column J matching the above
column A data, but separated by a "/"...see below

Column J

25743/12790/54689


What I want is to have the macro look in the "data" sheet and change
the column J in my "inventory sheet" to look like

Monster House/Cars/Saving Private Ryan



I have a list of about a 1000 ID's in A and the names in B in the data
sheet. Again, I only want to change the data in column J on the
inventory sheet.

Can you please help me?

Thanks.

Conor Finnegan

If the code as written works on the selection, that would be true, but
most/many people have moved beyond having to select - in which case it
wouldn't.

--
Regards,
Tom Ogilvy


"ufo_pilot" wrote:

Columns("J:J").Select

If I'm not mistaken,
adding this to your macro, before the find/Replace code, should limit the
search to column J
HTH


" wrote:

First off let me say thanks to anyone that can help me out here. I am
sure my question will be easily answered. I have searched this group
for over an over and haven't found a solution that works that well.
What I am having problem with is a Find & Replace macro updating about
a 1000 corresponding ID's and replacing those numbers with
corresponding names.

I have an Excel document where my data is in column J only that I want
to change. I can't limit the find&replace macro to just that column (I
just don't know code that well).

I have a sheet called "data" that looks like

Column A (ID) Column B (NAME)

25743 Monster House
12790 Cars
54689 Saving Private Ryan

I then have the other sheet that has a column J matching the above
column A data, but separated by a "/"...see below

Column J

25743/12790/54689


What I want is to have the macro look in the "data" sheet and change
the column J in my "inventory sheet" to look like

Monster House/Cars/Saving Private Ryan



I have a list of about a 1000 ID's in A and the names in B in the data
sheet. Again, I only want to change the data in column J on the
inventory sheet.

Can you please help me?

Thanks.

Conor Finnegan

Tom...Thanks for your help on this. I am still having issues with it
not replacing everything that falls within the "/"...any ideas as to
how I might fix this issue. Is it a formatting problem, etc? Please
let me know.

Thanks.

Conor

ps - an example view of this is Monster House/12790/Saving Private Ryan

Tom Ogilvy wrote:
Sub ABC()
Dim rng as Range, cell as Range
with Worksheets("Data")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
end with

for each cell in rng
Columns(10).Replace What:=cell.value, _
Replacement:=cell.offset(0,1).Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

Assumes the sheet where the changes to be made is the active sheet when you
run the macro.

--
Regards,
Tom Ogilvy


" wrote:

First off let me say thanks to anyone that can help me out here. I am
sure my question will be easily answered. I have searched this group
for over an over and haven't found a solution that works that well.
What I am having problem with is a Find & Replace macro updating about
a 1000 corresponding ID's and replacing those numbers with
corresponding names.

I have an Excel document where my data is in column J only that I want
to change. I can't limit the find&replace macro to just that column (I
just don't know code that well).

I have a sheet called "data" that looks like

Column A (ID) Column B (NAME)

25743 Monster House
12790 Cars
54689 Saving Private Ryan

I then have the other sheet that has a column J matching the above
column A data, but separated by a "/"...see below

Column J

25743/12790/54689


What I want is to have the macro look in the "data" sheet and change
the column J in my "inventory sheet" to look like

Monster House/Cars/Saving Private Ryan



I have a list of about a 1000 ID's in A and the names in B in the data
sheet. Again, I only want to change the data in column J on the
inventory sheet.

Can you please help me?

Thanks.

Conor Finnegan