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

 

[Fluff]

How about

Sub Nordicrx()
   Dim Ary As Variant
   Dim r As Long
   
   With Sheets("Data Location")
      Ary = .Range("Z2", .Range("AA" & Rows.Count).End(xlUp)).Value2
   End With
   With Sheets("SL ICR")
      .Cells.UnMerge
      For r = 1 To UBound(Ary)
         .Range(Ary(r, 2)).Value = Ary(r, 1)
      Next r
   End With
End Sub

 

[Nordicrx8]

How about

Sub Nordicrx()
   Dim Ary As Variant
   Dim r As Long
  
   With Sheets("Data Location")
      Ary = .Range("Z2", .Range("AA" & Rows.Count).End(xlUp)).Value2
   End With
   With Sheets("SL ICR")
      .Cells.UnMerge
      For r = 1 To UBound(Ary)
         .Range(Ary(r, 2)).Value = Ary(r, 1)
      Next r
   End With
End Sub

Fluff, I can always count on you to be on point my friend. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Nordicrx8]

You're welcome & thanks for the feedback.

Urgh - sorry to bother you again! I thought once I had the code I could alter it and get working for the other sheet too. basically it's the same exact thing, only I changed the cell reference to AB (there is still data in AA) and I changed the cheet to "SL CSR". It looks like it's still pulling in values from column AA - I tried playing around with some of the values below, but it started popping errors. I'm sure it's a simple item that needs to be updated, but Im not fully understanding how the below is working.

thanks so much again for you help!

Dim Ary As Variant
Dim r As Long

With Sheets("Data Location")
Ary = .Range("Z4", .Range("AB" & Rows.Count).End(xlUp)).Value2
End With
With Sheets("SL CSR")
.Cells.UnMerge
For r = 1 To UBound(Ary)
.Range(Ary(r, 2)).Value = Ary(r, 1)
Next r
End With
End Sub

 

[Fluff]

As you are looking at the 3rd column in the array, you need to use

.Range(Ary(r, 3)).Value = Ary(r, 1)

 

[Nordicrx8]

As you are looking at the 3rd column in the array, you need to use

.Range(Ary(r, 3)).Value = Ary(r, 1)

so that's what I thought, but when I change that value to a 3, it pops a runtime 1004 error. :/

 

[Fluff]

What is the exact error & what line is highlighted when you click debug?

 

[Nordicrx8]

What is the exact error & what line is highlighted when you click debug?

It's highlighting the line that I altered, and pops the following error: Run-Time Error '1004' Application-defined or object-defined error.

         .Range(Ary(r, 3)).Value = Ary(r, 1)

 

[Fluff]

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