VBA - Copy Paste/Indirect Cell

[Nordicrx8]

Hey All, Happy New Year!

I'm trying to alter a current macro to be a bit more user friendly when needing a cell reference update.

The below code activates when a macro button is pressed. It's basically going to the SL ICR tab in the workbook, unmerging column C, and entering in market location into a specific cell. Problem being, these cell locations can change from time to time, resulting in me having to go into the code to manually update the cell the names are entered into.

The solution I came up with was to have the market name and cell location data on a separate tab called "Data Location". Market Name is in column Z, and Cell Location is in AA. I need help with VBA code to look on the Data location tab and copy the market name to it's assigned cell to the "SL ICR" Tab.

Any help on this would be greatly appreciated!

Market	ICR
New England 1	C18
Capitol 1	C20
Canada 1	C22
Great Lakes 1	C24
Heartland 1	C26
Upper Midwest 1	C28
Greater Mid-Atlantic 1	C30
Mid-South 1	C36
Gulf Coast 1	C38
Texoma 1	C40
Florida 1	C42
South Atlantic 1	C48
Northern California 1	C53
Southern California 1	C59
Four Corners 1	C65
Pacific Northwest 1	C71

Sub Convert_SL()
'
' Convert_SL Macro
'
    Sheets("SL ICR").Select
    Cells.Select
    Selection.UnMerge
        
    Range("C18").Select
    ActiveCell.FormulaR1C1 = "New England 1"
    Range("C20").Select
    ActiveCell.FormulaR1C1 = "Capitol 1"
    Range("C22").Select
    ActiveCell.FormulaR1C1 = "Canada 1"
    Range("C24").Select
    ActiveCell.FormulaR1C1 = "Great Lakes 1"
    Range("C26").Select
    ActiveCell.FormulaR1C1 = "Heartland 1"
    Range("C28").Select
    ActiveCell.FormulaR1C1 = "Upper Midwest 1"
    Range("C30").Select
    ActiveCell.FormulaR1C1 = "Greater Mid-Atlantic 1"
    Range("C36").Select
    ActiveCell.FormulaR1C1 = "Mid-South 1"
    Range("C38").Select
    ActiveCell.FormulaR1C1 = "Gulf Coast 1"
    Range("C40").Select
    ActiveCell.FormulaR1C1 = "Texoma 1"
    Range("C42").Select
    ActiveCell.FormulaR1C1 = "Florida 1"
    Range("C48").Select
    ActiveCell.FormulaR1C1 = "South Atlantic 1"
    Range("C53").Select
    ActiveCell.FormulaR1C1 = "Northern California 1"
    Range("C59").Select
    ActiveCell.FormulaR1C1 = "Southern California 1"
    Range("C65").Select
    ActiveCell.FormulaR1C1 = "Four Corners 1"
    Range("C71").Select
    ActiveCell.FormulaR1C1 = "Pacific Northwest 1"
    
End Sub

 

[Nordicrx8]

In that case the value in col AB is not a valid range reference.

Ohhhh - I got it! one market has a cell value in AA, but not in AB. it looks like the VBA code didn't like the blank field. I assigned it a random cell value and it cleared the error and everything populated. thanks again for your help!

 

[Nordicrx8]

Duplicate reply - previous reply wasn't showing on my side. deleted.

 

[Fluff]

Ok, you can handle blank cells like

         If Ary(r, 3) <> "" Then .Range(Ary(r, 3)).Value = Ary(r, 1)