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: 20

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Based on the limited info received thus far, the following pic is all I can offer so far:



View attachment 42413


Here it is in XL2BB form:

Cell Formulas
RangeFormula
G2:G21G2=INDEX('Camera Conversion'!B:B,MATCH(Report!F2,'Camera Conversion'!A:A,0))



And here is the table for the conversions in XL2BB format:

bruntonomoV1.xlsm
AB
1Camera 1Zone 1A(West of J Drive Aisle)
2Camera 2Zone 1A(West of J Drive Aisle)
3Camera 3Zone 1A(West of J Drive Aisle)
4Camera 4Zone 1A(West of J Drive Aisle)
5Camera 5Zone 1A(West of J Drive Aisle)
6Camera 6Zone 1B(East of J Drive Aisle)
7Camera 7Zone 1B(East of J Drive Aisle)
8Camera 8Zone 1B(East of J Drive Aisle)
9Camera 9Zone 1B(East of J Drive Aisle)
10Camera 10Zone 1C(Annex/Expansion)
11Camera 11Zone 1C(Annex/Expansion)
12Camera 12Zone 1C(Annex/Expansion)
13Camera 13Zone 2A(East of R/S Belt)
14Camera 14Zone 2A(East of R/S Belt)
15Camera 15Zone 2A(East of R/S Belt)
16Camera 16Zone 2A(East of R/S Belt)
17Camera 17Zone 2A(East of R/S Belt)
18Camera 18Zone 2A(East of R/S Belt)
19UnknownZone Unknown
Camera Conversion
This worked! It's not a macro, but the formula does what I need it to do. Thank you for your help!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Bruno
is there anything consistent with the camera names? Does the list of camera names with locations come with the other data? If so we may be able to work with that, along the lines of what @johnnyL has done. I've got a macro for converting a table like in your reference image into a pair of columns that can then be used for a lookup. But to go further we do need to know what changes and what stays the same from one load to the next.
Hi there! johnny hit the nail on the head with what I am looking to do (setup and end result). It's just a very simple camera name association with a zone. There's no other information other than camera names and zone names.

Ultimately how I would like this to work is I paste the exported data from the reporting software into an Excel conversion spreadsheet (set up just like how johnny has it above - report sheet and conversion sheet) with the format of the End Result image I posted for the report sheet. Then I just run the macro, and it replaces everything in column F (End Result image) with the appropriate zone for each camera listed in column F.

I do apologize if this is confusing. I'm trying to be as specific as possible for both of you. It's a very simple function that I'm looking to produce. Just simply run the macro to swap the camera name for the appropriate zone name.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@johnnyL I'm sorry, but I cannot share actual camera names. I don't mean to be rude or difficult. Camera names are considered confidential information where I work. Some camera names have descriptor information that eludes to business practices and policy dictates that I can't share that information. That's why I provided generic names. I did provide zone names because those are very general and are not used by the client in any meaningful way other than a general descriptor. That's why I've been looking for a general solution that can be modified with specific details rather than a ready to deploy solution with all the details.
@bruntonomo I wasn't asking for specific camera names, I'm sorry if that is what you inferred. The 'Camera xx' format is fine, you can always fix the names later. Are you confident that you can complete the 'Camera Conversion' sheet with all the additional camera names and zones by yourself, now that you see how it is set up?
 

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@bruntonomo I wasn't asking for specific camera names, I'm sorry if that is what you inferred. The 'Camera xx' format is fine, you can always fix the names later. Are you confident that you can complete the 'Camera Conversion' sheet with all the additional camera names and zones by yourself, now that you see how it is set up?
Yes, I actually did that this morning. Everything is running and doing what it's supposed to do. It works great. I just wasn't familiar with a formula that could do what I needed it to do (and combining that with a macro). Thank you for your help! Again, I'm sorry I wasn't more clear with my description and information.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

So are you good with that formula that you can drag down the column or do you still need a macro?
 

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So are you good with that formula that you can drag down the column or do you still need a macro?
I can certainly drag the formula down. The macro would just be nice to be able to help keep the spreadsheet clean (not have to hide columns, etc.). It's by no means a deal breaker if it's not a macro.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok, here is a macro that you can use:

VBA Code:
Sub bruntonomoCameraNameConverter()     ' Formula to use =IFERROR(INDEX('Camera Conversion'!B:B,MATCH(Report!I2,'Camera Conversion'!A:A,0)),"")
'
    Dim CameraNameColumn            As String
    Dim ColumnToPutZoneNameInto     As String
    Dim LastRowOfCameraNameColumn   As Long
    Dim RowNumber                   As Long
'
    ColumnToPutZoneNameInto = "F"                                       ' <--- Set this to the column that you want the conversions to go to
'
    CameraNameColumn = "F"
'
    LastRowOfCameraNameColumn = Sheets("Report").Range(CameraNameColumn & Rows.Count).End(xlUp).Row ' Get the number of the last row used in the CameraNameColumn
'
    For RowNumber = 2 To LastRowOfCameraNameColumn
        Sheets("Report").Range(ColumnToPutZoneNameInto & RowNumber).Value = WorksheetFunction.Index(Sheets("Camera Conversion").Range("B:B"), _
            WorksheetFunction.Match(Sheets("Report").Range(CameraNameColumn & RowNumber).Value, Sheets("Camera Conversion").Range("A:A"), 0))
    Next
End Sub

I put a more fool proof formula to use, if you still wanted to go that route, in the comment of the first line. This will cover you if you drag the formula too far down in the column. ;)
 
Solution

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Ok, here is a macro that you can use:

VBA Code:
Sub bruntonomoCameraNameConverter()     ' Formula to use =IFERROR(INDEX('Camera Conversion'!B:B,MATCH(Report!I2,'Camera Conversion'!A:A,0)),"")
'
    Dim CameraNameColumn            As String
    Dim ColumnToPutZoneNameInto     As String
    Dim LastRowOfCameraNameColumn   As Long
    Dim RowNumber                   As Long
'
    ColumnToPutZoneNameInto = "F"                                       ' <--- Set this to the column that you want the conversions to go to
'
    CameraNameColumn = "F"
'
    LastRowOfCameraNameColumn = Sheets("Report").Range(CameraNameColumn & Rows.Count).End(xlUp).Row ' Get the number of the last row used in the CameraNameColumn
'
    For RowNumber = 2 To LastRowOfCameraNameColumn
        Sheets("Report").Range(ColumnToPutZoneNameInto & RowNumber).Value = WorksheetFunction.Index(Sheets("Camera Conversion").Range("B:B"), _
            WorksheetFunction.Match(Sheets("Report").Range(CameraNameColumn & RowNumber).Value, Sheets("Camera Conversion").Range("A:A"), 0))
    Next
End Sub

I put a more fool proof formula to use, if you still wanted to go that route, in the comment of the first line. This will cover you if you drag the formula too far down in the column. ;)
 

bruntonomo

Board Regular
Joined
Jul 29, 2018
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I just tried throwing the VBA code into the worksheet and when I run the macro it comes back with the error message...

Compile error: Invalid outside procedure

I've checked all the references in the code with the sheets, and everything looks good. Any thoughts?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Which line of code does it error on?
 

Forum statistics

Threads
1,136,772
Messages
5,677,637
Members
419,707
Latest member
Anna vib

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