Macro That Replaces Value With Column or Tab Name

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a list of roughly 150 cameras that are spread out throughout several buildings. When I get a report with a selected camera name, I would like to associate that with the appropriate zone. I would think the best way to do this would be to develop a macro that can be run to change all the camera name values in a given column to the name of a column header or tab name with which they are grouped. Please see the attached image.

In this case, when the macro would be run, any entries in a column that say Front Door, Hallway 1, Hallway 2, Lunch Room, or Bathrooms would be converted to Building 1. Room 1, Room 2, and Room 3 would be changed to Building 2.

I could go through each day and manually change these entries to what I want them to be, but it would take a considerable amount of time.
 

Attachments

  • Example.png
    Example.png
    14.2 KB · Views: 19

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
820
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Actually I don't think that error will show a line.

Which worksheet did you put the code into and is there any other code in that worksheet?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Actually I don't think that error will show a line.

Which worksheet did you put the code into and is there any other code in that worksheet?
Yeah, there was no code line in the error message.

The problem was me. Sorry about that. I went in and changed the part in the first line (formula) where it says Report!I2 to E2 as that's what I needed to do with the formula you gave me yesterday. I know you pointed out changing the CameraNameColumn and ColumnToPutZoneNameInto, but I figured I needed to change the part in the formula too. So that's my bad. Sorry about that. The macro works wonderfully! Thank you so much for your help!
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
820
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Sorry, that I2 was a leftover from me testing your code with different columns for the output. The I2 should be F2. You changing that should not have affected the running of the code because it is in a portion that is commented out.

You shouldn't need to change any of the code except possibly the two lines that have arrows in the comments. I set it up that way to make it easier to change for you, rather than searching the rest of the code for all places that would need to be changed.

Glad you got it working for you.
 

Forum statistics

Threads
1,136,735
Messages
5,677,462
Members
419,693
Latest member
divtjd

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