enter formula and autofill to end of data in the column of selected cell macro

mfarah

New Member
Joined
Aug 20, 2008
Messages
35
I'm trying to extend a formula that creates a column in the middle of a sheet depending on what month it is (so if its november the formula creates a new column after october and heads it "over due amount" - I need help with adding a sum formula tothis new column (e.g: =SUM(AX3+AV3+AT3+AQ3+AN3+AL3+AJ3)) the difficulty is that the formula starts with the current month e.g November for this month, but december next month) and only sums every 2nd cell back to April. Once the formula is added it needs to be autofilled to all the rows in that column uptill the data finishes...can anyone help
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mfarah

New Member
Joined
Aug 20, 2008
Messages
35
where ever the new column has been created starting at row three - which is where this has selected...

Sub AddColumns()
'partially working add column macro
Dim lastcol As Long, i As Long
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = lastcol To 1 Step -1
'test to see where the column should be placed
If InStr(1, Cells(1, i).Text, Application.Range("Current_Month").Value, vbTextCompare) Then
Columns(i).Insert

Columns(i).Select
End If
Next
ActiveCell.Offset(0, 0).Select
ActiveCell.Value = "Total Overdue"
ActiveCell.Offset(2, 0).Select
ActiveCell.Value = ""
' need to add a sum formula to this column but number of cells in row depending on month in year
'need to autofil to end of occupied row
End Sub
 

mfarah

New Member
Joined
Aug 20, 2008
Messages
35

ADVERTISEMENT

You posted a formula:

=SUM(AX3+AV3+AT3+AQ3+AN3+AL3+AJ3)

What cell would that be in?

This was just an example the current month = November (heading currently in cell AZ and 1st row of data in AZ3)
so the new column was creade in just after October column (currently AX) and so the new column would become AZ
the sum formula needs to cover all alternate columns to April currently AJ) These colums remain relitive to each other but the actual column start point will change as we progress through december and on to march 2010.

The alternate columns we dont want to sum is the amount invoiced each month.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If October is AX the April should be AL, I think. See if you can incorporate this into your existing code:

Code:
Sub Test()
    Dim LastRow As Long
    Dim Fmla As String
    Dim i As Long
    Fmla = "=SUM("
    With ActiveCell
        LastRow = .Offset(, -1).End(xlDown).Row
        For i = 38 To .Column - 1 Step 2
            Fmla = Fmla & "RC" & i & ","
        Next i
        Fmla = Left(Fmla, Len(Fmla) - 1) & ")"
        .FormulaR1C1 = Fmla
        .Formula = Replace(.Formula, "$", "")
        .Resize(LastRow - .Row + 1).Formula = .Formula
    End With
End Sub
 

mfarah

New Member
Joined
Aug 20, 2008
Messages
35

ADVERTISEMENT

If October is AX the April should be AL, I think. See if you can incorporate this into your existing code:

Code:
Sub Test()
    Dim LastRow As Long
    Dim Fmla As String
    Dim i As Long
    Fmla = "=SUM("
    With ActiveCell
        LastRow = .Offset(, -1).End(xlDown).Row
        For i = 38 To .Column - 1 Step 2
            Fmla = Fmla & "RC" & i & ","
        Next i
        Fmla = Left(Fmla, Len(Fmla) - 1) & ")"
        .FormulaR1C1 = Fmla
        .Formula = Replace(.Formula, "$", "")
        .Resize(LastRow - .Row + 1).Formula = .Formula
    End With
End Sub

nearly there it does add the forulas so great thank you :) however can we have it referencing colum A to find the last row of data as it doesn't seem to be filling all the way down
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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