Switch from Absolute reference to Relative Reference - VBA

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hello, I am working on a model and have VBA code:
  1. Copy/paste a range ("A21:L30") from a template worksheet to multiple workbooks that begin with "Labor BOE", multiple times based on a number in cell "L2"

For example, if "Labor BOE 1" worksheet has a "3" in cell L2, then it will copy/paste the range ("A21:L30") from the template to this worksheet 3 times

Code:
Sub InsertTasks()
Application.ScreenUpdating = False
Dim num As Long
Dim i As Long
Dim b As Long
 Dim Lastrow As Long
    For i = 1 To Sheets.Count
        If Left(Sheets(i).Name, 9) = "Labor BOE" Then
            Lastrow = Sheets(i).Cells(Rows.Count, "L").End(xlUp).Row + 1
            num = Sheets(i).Range("L2").Value
            For b = 1 To num
                Lastrow = Sheets(i).Cells(Rows.Count, "L").End(xlUp).Row + 1
                Sheets("Template - Tasks").Range("A21:L30").Copy Destination:=Sheets(i).Range("A" & Lastrow)
            Next
        
        End If
    Next
Application.ScreenUpdating = True
  
End Sub



2. Then, I am searching column A, and every time it finds a number, it will insert that many rows below and copy the formulas down in columns "C:L"

Code:
Sub InsertRows()
Dim End_Row As Long, n As Long, Ins As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
    If Left(sh.Name, 9) = "Labor BOE" Then
        
        End_Row = sh.Range("L" & Rows.Count).End(xlUp).Row
        
        Application.ScreenUpdating = False
        For n = End_Row To 3 Step -1
            Ins = sh.Cells(n, "A").Value
            
            If Ins > 0 Then
                sh.Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
                sh.Range("C" & n & ":L" & n).Copy Destination:=sh.Range("C" & n + 1 & ":L" & n + Ins)
            End If
        Next n
        
    End If
Next sh
MsgBox "BOE Generation Complete"
End Sub



But... The array formula used in column D is extracting a unique list from the "Staffing Plan" worksheet and so it contains a mix of absolute/relative cell references. The reference that is giving me issues is the "$D$26:$D26". When the range is copy/pasted a second time on one sheet, it is stuck at "$D$26:$D36", but I need it to be "$D$36:$D36". When it is posted a third time on one sheet, it is stuck at "$D$26:$D46", but I need it to be "$D$46:$D46".

{=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D26, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"") }



Is it possible to add code to the first macro ("Insert Tasks") to unlock the cell reference "$D$26:$D26" to "D26:$D26", copy/paste it, and then switch back to "$D$26:$D26" ? Or do you have any other thoughts on how to get around this issue?
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I am not sure why a single cell, "$D$26:$D$26" is being used for a CountIf() function to begin with. But it seems like just changing the formula as it is in the worksheet would be the easy solution, since Excel will automatically use relative references once the $ symbol is removed. If you try that, don't forget to use Ctrl + Shft + Enter to re-initialize your array formula. You might need to actually restate your array formula in code to do what you are describing.
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
The purpose of that array formula is to extract a unique list of values from a range when a criteria is met. When that formula is dragged down, it changes as shown below:

D27: {=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D26, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"") } = Unique Occurrence 1

D28: {=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D27, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"") } = Unique Occurrence 2

D29: {=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D28, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"") } = Unique Occurrence 3



Unfortunately, I need to drag the formula to extract the full list of unique values, so I am unable to remove the absolute reference all together.

I believe the best option is to use a VBA to accomplish the functionality outlined below:

1. Start with this reference as a relative reference (D26), formula shown below:

{=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF(D26:$D26, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"") } = Unique Occurrence 1


2. Copy/paste the range multiple times [accomplished by VBA code below]


Code:
Sub InsertTasks()
 Application.ScreenUpdating = False
 Dim num As Long
 Dim i As Long
 Dim b As Long
 Dim Lastrow As Long
    For i = 1 To Sheets.Count
        If Left(Sheets(i).Name, 9) = "Labor BOE" Then
            Lastrow = Sheets(i).Cells(Rows.Count, "L").End(xlUp).Row + 1
            num = Sheets(i).Range("L2").Value
            For b = 1 To num
                Lastrow = Sheets(i).Cells(Rows.Count, "L").End(xlUp).Row + 1
                Sheets("Template - Tasks").Range("A21:L30").Copy Destination:=Sheets(i).Range("A" & Lastrow)
            Next

        End If
    Next
 Application.ScreenUpdating = True

 End Sub



3. Then lock the formula reference as shown below:

{=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D26, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"") } = Unique Occurrence 1

4. Then insert rows/drag the formula down to extract unique list of values on "Staffing Plan"

Code:
Sub InsertRows()
 Dim End_Row As Long, n As Long, Ins As Long
 Dim sh As Worksheet
 For Each sh In ActiveWorkbook.Sheets
    If Left(sh.Name, 9) = "Labor BOE" Then

End_Row = sh.Range("L" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
        For n = End_Row To 3 Step -1
            Ins = sh.Cells(n, "A").Value

            If Ins > 0 Then
sh.Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
sh.Range("C" & n & ":L" & n).Copy Destination:=sh.Range("C" & n + 1 & ":L" & n + Ins)
            End If
        Next n

    End If
 Next sh
MsgBox "BOE Generation Complete"
 End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,891
Messages
5,598,680
Members
414,254
Latest member
MarieCo

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