Bulk Replace with VBA

[nufernandes78]

so imagine an image displayed in numbered cells... meaning all the cells are scattered on the excel sheet. If you were to follow the numbers and draw a line from 1 to 2, to 3 it would give you an image...that image would have numbers starting at 1, and depending on how large the image is it could end at 2000. each cell has one number in it (1-2000)

i want to replace those numbers with a structed set of numbers in 2 columns I've created,
Column A has numbers 1 through 2000
Column B has corresponding numbers I want the image changed to, for example in cell A:171 is number 171, but in B:171 the number is 1001.... I want to replace 171 in the image with 1001


Can someone help write a VBS code to help with this?

 

[Z51]

The following is the "brute force" method of looping through A1:A2000 and using the Replace function to replace all cells in your 'picture' range with the corresponding values in B1:B2000. To do 2000 replacement calls over a range of about 37000 cells in my test sheet took about 16 seconds. Not very efficient, but it should work. For a (MUCH) faster option, I'd recommend a ScriptingDictionary object like provided by Leith Ross to, well, me over 10 years ago here: More efficient alternative to Find-Replace Loop?

Sub nufernandes()
Dim r As Range

For Each r In Range("A1:A2000")
    Range("D1:BD700").Replace What:=r.Value, Replacement:=r.Offset(, 1).Value, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next r
End Sub

 

[nufernandes78]

Thank you for the reply.
i am very close....
column A has numbers from 1 to 2000 in order.
Column B has numbers 1 through 170, then 1001 through 1170, 2001 through 2170, 3001 through 3170 and so on.

when the code finds 171 from column A it changes it to 1001 as it should, but then it ends up finding 1001 from the column A again and it changes it to 5150 which is where i run into an issue.
when both numbers exist in both columns i get a bad result. is there something i can add to the code you already provided that keeps it from doing that?

 

[Z51]

Is it possible to start column B at a number higher than column A will ever be, like 10001-10170, 11001-11170, etc.?

 

[nufernandes78]

not for this exercise. what i did was i added an "A" to the 1001 through 1170 in column B which are the only numbers that repeat in Column A.
after running the Macro, i copy the range and run it again on sheet 2, where Column A is the A1001 through A1170, and column B is the numbers i need 1001 through 1170, and it works.

i have to run the macro twice, but it only takes a few seconds each time...

thank you for all your help.

 

[Z51]

You're welcome

 

[nufernandes78]

BTW, just in case your wondering what this is all about.... DMX lighting... it works in universes of 170 pixels per universe and each pixel is 3 channels so 510 channels per universe.
in order to re-number a prop to accommodate the correct amount of lights so that the software can read the programming of the lights, it has to be laid out this way.

here is a link to my show if you are interested. a lot of the props with lights in them were mapped this way.

 

[Z51]

That is great, thanks for sharing. My cousin does light/music shows for Halloween and Christmas, so I understand how much time and effort goes into setting them up! It's always interesting learning what others are using Excel for outside the office.