Remove Absolute References

[datauser]

Hello!

I have a working macro that calculates correctly and starts where I need it to, but I need to remove all the absolute references for the formula so that I can apply the formula down a column. The starting point for my formula will be D5, the ending row number will be the same(5), but ending column will be dynamic.

I would like this part of the code:
formulaStr = "=SUMPRODUCT(--(MOD(COLUMN(" & ws.Cells(targetRow, 4).Address & ":" & ws.Cells(targetRow, lastCol).Address & ") - COLUMN(" & ws.Cells(targetRow, 4).Address & ") + 1, 2) = 1)," & ws.Cells(targetRow, 4).Address & ":" & ws.Cells(targetRow, lastCol).Address & ")"
to work like this: =SUMPRODUCT(--(MOD(COLUMN(D5:AH5)-COLUMN(D5)+1,2)=1),D5:AH5) so when I drag the formula down, the cell references will also change
My attempt to define the start of the formula, but have the ending column be dynamic. formulaStr = "=SUMPRODUCT(--(MOD(COLUMN(D5:& LastCol& 5)) - COLUMN(D5) + 1, 2) = 1),D5:& LastCol & 5)"

Sub InsertSumProductFormula3()

Dim ws As Worksheet
Dim lastCol As Long
Dim lastRow As Long
Dim formulaCell As Range
Dim formulaStr As String
Dim targetRow As Long

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Week to Week")

' Specify the target row where you want to insert the formula (e.g., row 5)
targetRow = 5 ' Change to your desired row

' Find the last column with data in the target row
lastCol = ws.Cells(targetRow, ws.Columns.Count).End(xlToLeft).Column

' Find the last row with data in column A (adjust if needed)
lastRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row

' Define the cell where the formula will be added (first empty cell in the next row)
Set formulaCell = ws.Cells(targetRow, lastCol + 1) ' Adjust to your desired column

' Construct the dynamic SUMPRODUCT formula
formulaStr = "=SUMPRODUCT(--(MOD(COLUMN(" & ws.Cells(targetRow, 4).Address & ":" & ws.Cells(targetRow, lastCol).Address & ") - COLUMN(" & ws.Cells(targetRow, 4).Address & ") + 1, 2) = 1)," & ws.Cells(targetRow, 4).Address & ":" & ws.Cells(targetRow, lastCol).Address & ")"

' Insert the formula
formulaCell.Formula = formulaStr

MsgBox "Formula added in " & formulaCell.Address

End Sub

 

[HighAndWilder]

Hello!

I have a working macro that calculates correctly and starts where I need it to, but I need to remove all the absolute references for the formula so that I can apply the formula down a column. The starting point for my formula will be D5, the ending row number will be the same(5), but ending column will be dynamic.

I would like this part of the code:
formulaStr = "=SUMPRODUCT(--(MOD(COLUMN(" & ws.Cells(targetRow, 4).Address & ":" & ws.Cells(targetRow, lastCol).Address & ") - COLUMN(" & ws.Cells(targetRow, 4).Address & ") + 1, 2) = 1)," & ws.Cells(targetRow, 4).Address & ":" & ws.Cells(targetRow, lastCol).Address & ")"
to work like this: =SUMPRODUCT(--(MOD(COLUMN(D5:AH5)-COLUMN(D5)+1,2)=1),D5:AH5) so when I drag the formula down, the cell references will also change
My attempt to define the start of the formula, but have the ending column be dynamic. formulaStr = "=SUMPRODUCT(--(MOD(COLUMN(D5:& LastCol& 5)) - COLUMN(D5) + 1, 2) = 1),D5:& LastCol & 5)"

Sub InsertSumProductFormula3()

Dim ws As Worksheet
Dim lastCol As Long
Dim lastRow As Long
Dim formulaCell As Range
Dim formulaStr As String
Dim targetRow As Long

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Week to Week")

' Specify the target row where you want to insert the formula (e.g., row 5)
targetRow = 5 ' Change to your desired row

' Find the last column with data in the target row
lastCol = ws.Cells(targetRow, ws.Columns.Count).End(xlToLeft).Column

' Find the last row with data in column A (adjust if needed)
lastRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row

' Define the cell where the formula will be added (first empty cell in the next row)
Set formulaCell = ws.Cells(targetRow, lastCol + 1) ' Adjust to your desired column

' Construct the dynamic SUMPRODUCT formula
formulaStr = "=SUMPRODUCT(--(MOD(COLUMN(" & ws.Cells(targetRow, 4).Address & ":" & ws.Cells(targetRow, lastCol).Address & ") - COLUMN(" & ws.Cells(targetRow, 4).Address & ") + 1, 2) = 1)," & ws.Cells(targetRow, 4).Address & ":" & ws.Cells(targetRow, lastCol).Address & ")"

' Insert the formula
formulaCell.Formula = formulaStr

MsgBox "Formula added in " & formulaCell.Address

End Sub

View attachment 118642

Does this work.

Note the .Address(False, False) although .Address(True, False) would have locked the column.

formulaStr = "=SUMPRODUCT(--(MOD(COLUMN(" & ws.Cells(targetRow, 4).Address(False, False) & ":" & ws.Cells(targetRow, _
  lastCol).Address(False, False) & ") - COLUMN(" & ws.Cells(targetRow, 4).Address(False, False) & ") + 1, 2) = 1)," & _
  ws.Cells(targetRow, 4).Address(False, False) & ":" & ws.Cells(targetRow, lastCol).Address(False, False) & ")"

 

[datauser]

This works! Thank you so much. This completely stumped me! Working with this VBA would you be able to assist in dragging the formula down the columns?

 

[HighAndWilder]

This works! Thank you so much. This completely stumped me! Working with this VBA would you be able to assist in dragging the formula down the columns?

You are nearly there:

You just need to change the row numbers.

ws.Range("AI19:AI30").Formula2 = formulaStr