Using column count to end of range

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
92
Try this

Code:
Sub Update_Actuals()
  Dim i As Long, lc As Long, lr As Long
  lc = Cells(13, Columns.Count).End(xlToLeft).Column  'last column
  lr = Range("AG" & Rows.Count).End(xlUp).Row         'last row
  If lc < Columns("AG").Column Then lc = Columns("AG").Column
  If lr < 13 Then lr = 13
  Range(Cells(13, "AG"), Cells(lr, lc)).ClearContents
  For i = 0 To DateDiff("m", Range("E5"), Range("E7"))
    Cells(13, Columns("AG").Column + i) = WorksheetFunction.EDate(Range("E5"), i)
    Range("Z31:Z78").Copy Cells(14, Columns("AG").Column + i)
  Next
[COLOR=#0000ff]  Range("AK7") = WorksheetFunction.Sum(Range("AG14").Resize(63, i))[/COLOR]
End Sub
This is great! Can we make it a formula in the cell? instead of the value?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,222
Office Version
2007
Platform
Windows
Try

Code:
Range("AK7").Formula = "=sum(" & Range("AG14").Resize(48, i).Address & ")"
 

Watch MrExcel Video

Forum statistics

Threads
1,089,973
Messages
5,411,586
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top