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

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.
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))