Macro Help

Closed
Report
Posts
2
Registration date
Friday November 30, 2012
Status
Member
Last seen
December 3, 2012
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I wrote the below macro but my problem is the number of rows change each month so i would like to have an autofill the formula from column z to Aj until the last row of the data. Can some please help

Sheets("Detailed YTD 2012").Select
Range("Z2").FormulaR1C1 = "=+RC[-4]-RC[-6]+1"
Range("Z2").NumberFormat = "General"
Range("AB2").FormulaR1C1 = "=RC[-1]-RC[-8]+1"
Range("AB2").NumberFormat = "General"
Range("AC2").FormulaR1C1 = "=IF(RC[-8]<2012,RC[-7]-366,RC[-9])"
Range("AD2").FormulaR1C1 = "=IF(RC[-9]<2012,RC[-8],RC[-10]+366)"
Range("AE2").FormulaR1C1 = "=RC[-4]-RC+1"
Range("AE2").FormulaR1C1 = "=RC[-4]-RC[-2]+1"
Range("AE2").NumberFormat = "General"
Range("AF2").FormulaR1C1 = "=RC[-2]-RC[-3]"
Range("AF2").NumberFormat = "General"
Range("AG2").FormulaR1C1 = "=IF(RC[-7]>728,RC[-2]/RC[-1],RC[-5]/RC[-7])"
Range("AH2").FormulaR1C1 = "=IF(RC[-1]<100%,RC[-1],100%)"
Range("AI2").FormulaR1C1 = "=RC[-1]*RC[-27]"
Range("AJ2").FormulaR1C1 = "=RC[-28]-RC[-1]"
Range("Z2:AJ2").Select
Range("Z2").Select
 Selection.AutoFill Destination:=Range("Z2:Z13282")
    Range("Z2:Z13282").Select
    Range("AB2:AJ2").Select
    Selection.AutoFill Destination:=Range("AB2:AJ13282"), Type:=xlFillDefault




2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this

Public Sub Fillin()
   Dim lastRowUsed As Long
   
   Sheets("Detailed YTD 2012").Select
   lastRowUsed = getItemLocation("*", Cells)
   
   Range("Z2").FormulaR1C1 = "=+RC[-4]-RC[-6]+1"
   Range("Z2").NumberFormat = "General"
   Range("AB2").FormulaR1C1 = "=RC[-1]-RC[-8]+1"
   Range("AB2").NumberFormat = "General"
   Range("AC2").FormulaR1C1 = "=IF(RC[-8]<2012,RC[-7]-366,RC[-9])"
   Range("AD2").FormulaR1C1 = "=IF(RC[-9]<2012,RC[-8],RC[-10]+366)"
   Range("AE2").FormulaR1C1 = "=RC[-4]-RC+1"
   Range("AE2").FormulaR1C1 = "=RC[-4]-RC[-2]+1"
   Range("AE2").NumberFormat = "General"
   Range("AF2").FormulaR1C1 = "=RC[-2]-RC[-3]"
   Range("AF2").NumberFormat = "General"
   Range("AG2").FormulaR1C1 = "=IF(RC[-7]>728,RC[-2]/RC[-1],RC[-5]/RC[-7])"
   Range("AH2").FormulaR1C1 = "=IF(RC[-1]<100%,RC[-1],100%)"
   Range("AI2").FormulaR1C1 = "=RC[-1]*RC[-27]"
   Range("AJ2").FormulaR1C1 = "=RC[-28]-RC[-1]"
   Range("Z2:AJ2").Select
   Range("Z2").Select
   
   
    Selection.AutoFill Destination:=Range("Z2:Z" & lastRowUsed)
       Range("Z2:Z" & lastRowUsed).Select
       Range("AB2:AJ2").Select
       Selection.AutoFill Destination:=Range("AB2:AJ" & lastRowUsed), Type:=xlFillDefault
End Sub

Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                   
   'find the first/last row/column  within a range for a specific string
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
         
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
         
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function

0
Posts
2
Registration date
Friday November 30, 2012
Status
Member
Last seen
December 3, 2012

I used the above macro but now it exceeds the last row and fills up the blank rows starting with col z .
for eg if the last row used in row 300 the macro now exceeds line 300 and fills up upto line 500.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Are you looking for last used row or last used column Z cell ?
The code is to find out what is the last used cell in the sheet. If you want to see what is the last used cell in Z is then
change
lastRowUsed = getItemLocation("*", Cells)
to
lastRowUsed = getItemLocation("*", COLUMNS(26))
0