Macro help to change cell relative reference

[dalameda]

Hello All ~
I am writing a macro to prepare a tab for the next year's data. I have been searching this forum and have found many answers to issues a long the way. I have searched and tried many things but cant get this last part to work correctly.
Right now all the coding that is active (shown below) is working. The only part I cant figure out how to change is to get the SUM formula created in the new YTD column to have the the first column be absolute and the rest of the formula to be relative so I can add additional coding to copy the formula down. example. when i currently run the macro, it creates a new YTD column in column AK and I want a formula to go into AK5 that is basically =SUM($AI5:AJ5) and rignt now the formula that gets entered is SUM($AI$5:$AJ$5) (of course the column reference would change each year)

I have tried the ConvertFormula coding (inactive code that is in bold, red below) but there is something that I dont have right as i keep getting "object required" error messages...I have tried setting up a second object and such but to no avail.

Any help would be appreciated.



Sub MatFlowNewYear()
'
' Prepares the Mat Flow tab for the new year's data
'
'Find the last column with data in row 4 (the row showing the month) and delete the thin column that is 2 columns to the left
Range("IV4").End(xlToLeft).Select
ActiveCell.EntireColumn.Offset(0, -2).Delete

'Insert three new columns to left of "current month's" data column
Range("IV4").End(xlToLeft).Select
ActiveCell.EntireColumn.Insert
Range("IV4").End(xlToLeft).Select
ActiveCell.EntireColumn.Offset(0, -1).Insert
Range("IV4").End(xlToLeft).Select
ActiveCell.EntireColumn.Offset(0, -2).Insert

'Change the column width of the two left columns created
Range("IV4").End(xlToLeft).Select
ActiveCell.EntireColumn.Offset(0, -2).Select
ActiveCell.ColumnWidth = 2
Range("IV4").End(xlToLeft).Select
ActiveCell.EntireColumn.Offset(0, -3).Select
ActiveCell.ColumnWidth = 2

'change the color of the far left empty column just created
Range("IV4").End(xlToLeft).Select
ActiveCell.Offset(-3, -3).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Interior.color = RGB(0, 0, 0)

'Label the column to the left of "current month's" data with a YTD and put the new year in the cell above that
Range("IV4").End(xlToLeft).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "YTD"
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=YEAR(R[1]C[1])"

'Enter SUM formula in new YTD column
Dim lCol As Variant
Range("IV5").End(xlToLeft).Select
ActiveCell.Offset(0, -2).Select
lCol = ActiveCell.Address
'lCol.Formula = Application.ConvertFormula(Formula:=lCol.Formula, fromreferencestyle _
:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowRelColumn)

Dim fCol As Variant
Range("IV5").End(xlToLeft).Select
ActiveCell.Offset(0, -3).Select
fCol = ActiveCell.Address
'fCol.Formula = Application.ConvertFormula(Formula:=fCol.Formula, fromreferencestyle _
:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
Range("IV5").End(xlToLeft).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = "=SUM(" & fCol & ":" & lCol & ")"
'ActiveCell.FormulaR1C1 = "=SUM(INDIRECT(RC-2:RC[-1]))"
'Set fCol = Nothing
'Set lCol = Nothing


MsgBox "Completed! ", vbInformation, "Mat Flow Tab"

End Sub

 

[Jerry Sullivan]

Hello,

There are two problems with the code in red font that you posted.

Dim lCol As Variant
lCol = ActiveCell.Address
lCol.Formula = Application.ConvertFormula(Formula:=lCol.Formula, _
    fromReferenceStyle:=xlA1, toReferenceStyle:=xlA1, _ 
    ToAbsolute:=xlRelRowRelColumn)

1. There isn't a constant called xlRelRowRelColumn, instead use xlRelative

2. The lCol variable you are using is an address string. The ConvertFormula needs a range object. Try something like this instead.

Dim rngX As Range
Set rngX = ActiveCell
rngX.Offset(1).Formula = Application.ConvertFormula _
    (Formula:=rngX.Formula, fromReferenceStyle:=xlA1, _
    toReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

I haven't looked at the rest of your code to see if it will work with these changes, but those two changes will be a step in that direction.

 

[dalameda]

Jerry,
Thank you.

I tried the coding you suggested but get the same error that says "Application-defined or Object-defined error" and it highlightes the SUM formula i am trying to enter into the YTD column:
ActiveCell.Formula= "=SUM(" & fCol & ":" & lCol & ")"

Is there no way to get the coding to change formula so the column is absolute and the row is relative?

Code:
'Enter SUM formula in new YTD column
Dim lCol As Range
Range("IV5").End(xlToLeft).Select
ActiveCell.Offset(0, -2).Select
Set lCol = ActiveCell
lCol.Offset(1).Formula = Application.ConvertFormula _
(Formula:=lCol.Formula, fromreferencestyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

Dim fCol As Range
Range("IV5").End(xlToLeft).Select
ActiveCell.Offset(0, -3).Select
Set fCol = ActiveCell
fCol.Offset(1).Formula = Application.ConvertFormula _
(Formula:=fCol.Formula, fromreferencestyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
Range("IV5").End(xlToLeft).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Formula = "=SUM(" & fCol & ":" & lCol & ")"

 

[Jerry Sullivan]

Your code can be simplified by eliminating the use of ActiveCell and Selection by referencing the ranges more directly as shown in the example below.

I probably misinterpreted what your worksheet looks like before running, but the revised code below will show you how you can streamline things.

You can create a formula that has relative and absolute references as shown below in the blue font.

The desired location of your lCol and fCol formulas confused me...
It looks like you are trying to take existing formulas in those cells and modify them with ConvertFormula,
but the cells being referenced look like they fall in your new columns which won't have formulas.

The "lCol Formula" and "fCol Formula" strings are just placeholders so you can adjust as needed.

Sub MatFlowNewYear_1()
    With Cells(4, Columns.Count).End(xlToLeft)
        .Offset(0, -2).EntireColumn.Delete
        .Offset(0, -1).EntireColumn.Resize(, 3).Insert
        .Offset(0, -4).Resize(, 2).ColumnWidth = 2
        .Offset(, -3).EntireColumn _
            .Interior.Color = RGB(0, 0, 0)
        .Offset(0, -1) = "YTD"
        .Offset(-1, -1).FormulaR1C1 = "=YEAR(R[1]C[1])"
        .Offset(1, -3).FormulaR1C1 = "lCol formula"
        .Offset(1, -2).FormulaR1C1 = "fCol formula"
        .Offset(1, -1).FormulaR1C1 = "=SUM(RC" & .Column - 3 & ":RC[-1])"
    End With
    MsgBox "Completed! ", vbInformation, "Mat Flow Tab"
End Sub

Please give this a try and see if it helps.

If you can post a small screen shot showing starting layout and desired result it will make it easier to follow.

 

[dalameda]

Jerry,
Thank you again for your help and thank you for the code simplification suggestions. With some minor changes I was able to get the SUM formula coding you used in your last post to work!!

FYI - the fCol and lCol were just my attempts in identifying which cell I wanted the sum formula to start and end and changing the relative/absolute reference of each....but I was able to pull those out completely using your suggestion.

This is great stuff! Thank you for your time!!
Dan