Bulk Replace with VBA

nufernandes78

New Member
Joined
Aug 4, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Z51

Well-known Member
Joined
Dec 12, 2007
Messages
504
Office Version
  1. 365
  2. 2016
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?

VBA Code:
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

New Member
Joined
Aug 4, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 12, 2007
Messages
504
Office Version
  1. 365
  2. 2016
Is it possible to start column B at a number higher than column A will ever be, like 10001-10170, 11001-11170, etc.?
 

nufernandes78

New Member
Joined
Aug 4, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

nufernandes78

New Member
Joined
Aug 4, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 12, 2007
Messages
504
Office Version
  1. 365
  2. 2016
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. :)
 

Forum statistics

Threads
1,171,185
Messages
5,874,222
Members
433,037
Latest member
Not Moose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top