0

I've created a program that is supposed to select an entire column and autofill it to the end starting with a formula that will ALWAYS be in a fixed spot on the column. However, the issue is that in terms of row numbers, the number changes daily, so I can't hardcode an ending range.

I tried to do this:

    Range("W11").Select
ActiveCell.FormulaR1C1 = _
    "=IF([@COMPANY]&[@Whse]=""R01""," & Chr(10) & "   R[-1]C-IF([@BOQty]="""",0,[@BOQty])" & Chr(10) & "   +IF([@[R01-PO QTY]]="""",0,[@[R01-PO QTY]])" & Chr(10) & "   +IF([@[R01-ALC QTY]]="""",0,[@[R01-ALC QTY]])" & Chr(10) & "   +IF([@[R01-JOB QTY]]="""",0,[@[R01-JOB QTY]])" & Chr(10) & "   +IF([@[R01-GIT QTY]]="""",0,[@[R01-GIT QTY]])," & Chr(10) & "R[-1]C)"
Range("W11").Select
Selection.AutoFill Destination:=Range("W11:W")

But it returns a "Method Range of Object _Global failed.

What do I do instead? How do I select the entire column AFTER W11 in this case?

majinvegito123
  • 25
  • 5
  • Also relevant https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Warcupine May 10 '22 at 18:18
  • 1
    Since, column "W:W" should be filled with the formula result, the last row should be calculated based on another column. So, based on what such column would you like to calculate the last filled row? Then, the formula can be applied on the entire range, without iteration. – FaneDuru May 10 '22 at 18:29

1 Answers1

0

I got this working. If anyone is wondering what I did, I did it in a sort of roundabout way. I first created a function called GetLastRow

Function GetLastRow(Strt As String, Cur As String)

'''''
'Get the row of the last line of data
'''''
GetLastRow = Range(Strt, Range(Cur).End(xlDown)).Rows.Count
If GetLastRow > 1000000 Then
    GetLastRow = ElimAlpha(Cur)
End If

End Function

I then created some Dims to get the last row for EACH of my columns. Worked beautifully.

    Sub RunningBalances()

Dim NumRows01 As Integer
Dim NumRows02 As Integer
Dim NumRowsRDS As Integer
Dim NumRows1 As Integer
Dim NumRowsPDS As Integer
'
' RunningBalances Macro
'

'
'CompanyR W/H 01
NumRows01 = GetLastRow("W11", "W11" & VBA.CStr(StrtPOs))
    Range("W11").Select
    ActiveCell.FormulaR1C1 = _
        "=IF([@COMPANY]&[@Whse]=""R01""," & Chr(10) & "   R[-1]C-IF([@BOQty]="""",0,[@BOQty])" & Chr(10) & "   +IF([@[R01-PO QTY]]="""",0,[@[R01-PO QTY]])" & Chr(10) & "   +IF([@[R01-ALC QTY]]="""",0,[@[R01-ALC QTY]])" & Chr(10) & "   +IF([@[R01-JOB QTY]]="""",0,[@[R01-JOB QTY]])" & Chr(10) & "   +IF([@[R01-GIT QTY]]="""",0,[@[R01-GIT QTY]])," & Chr(10) & "R[-1]C)"
    Selection.AutoFill Destination:=Range("W11" & ":W" & NumRows01)
'CompanyR W/H 02
NumRows02 = GetLastRow("AF11", "AF11" & VBA.CStr(StrtPOs))
    Range("AF11").Select
    ActiveCell.FormulaR1C1 = _
        "=IF([@COMPANY]&[@Whse]=""R02""," & Chr(10) & "   R[-1]C-IF([@BOQty]="""",0,[@BOQty])" & Chr(10) & "   +IF([@[R02-PO QTY]]="""",0,[@[R02-PO QTY]])" & Chr(10) & "   +IF([@[R02-ALC QTY]]="""",0,[@[R02-ALC QTY]])" & Chr(10) & "   +IF([@[R02-JOB QTY]]="""",0,[@[R02-JOB QTY]])" & Chr(10) & "   +IF([@[R02-GIT QTY]]="""",0,[@[R02-GIT QTY]])," & Chr(10) & "R[-1]C)"
    Selection.AutoFill Destination:=Range("AF11" & ":AF" & NumRows02)
'CompanyR W/H DS
NumRowsRDS = GetLastRow("AO11", "AO11" & VBA.CStr(StrtPOs))
    Range("AO11").Select
    ActiveCell.FormulaR1C1 = _
        "=IF([@COMPANY]&[@Whse]=""RDS""," & Chr(10) & "   R[-1]C-IF([@BOQty]="""",0,[@BOQty])" & Chr(10) & "   +IF([@[RDS-PO QTY]]="""",0,[@[RDS-PO QTY]])" & Chr(10) & "   +IF([@[RDS-ALC QTY]]="""",0,[@[RDS-ALC QTY]])" & Chr(10) & "   +IF([@[RDS-JOB QTY]]="""",0,[@[RDS-JOB QTY]])" & Chr(10) & "   +IF([@[RDS-GIT QTY]]="""",0,[@[RDS-GIT QTY]])," & Chr(10) & "R[-1]C)"
    Selection.AutoFill Destination:=Range("AO11" & ":AO" & NumRowsRDS)
'CompanyP W/H 1
NumRows1 = GetLastRow("AX11", "AX11" & VBA.CStr(StrtPOs))
    Range("AX11").Select
    ActiveCell.FormulaR1C1 = _
        "=IF([@COMPANY]&[@Whse]=""P1""," & Chr(10) & "   R[-1]C-IF([@BOQty]="""",0,[@BOQty])" & Chr(10) & "   +IF([@[P1-PO QTY]]="""",0,[@[P1-PO QTY]])" & Chr(10) & "   +IF([@[P1-ALC QTY]]="""",0,[@[P1-ALC QTY]])" & Chr(10) & "   +IF([@[P1-JOB QTY]]="""",0,[@[P1-JOB QTY]])" & Chr(10) & "   +IF([@[P1-GIT QTY]]="""",0,[@[P1-GIT QTY]])," & Chr(10) & "R[-1]C)"
    Selection.AutoFill Destination:=Range("AX11" & ":AX" & NumRows1)
'CompanyP W/H DS
NumRowsPDS = GetLastRow("BG11", "BG11" & VBA.CStr(StrtPOs))
    Range("BG11").Select
    ActiveCell.FormulaR1C1 = _
        "=IF([@COMPANY]&[@Whse]=""R02""," & Chr(10) & "   R[-1]C-IF([@BOQty]="""",0,[@BOQty])" & Chr(10) & "   +IF([@[PDS-PO QTY]]="""",0,[@[PDS-PO QTY]])" & Chr(10) & "   +IF([@[PDS-ALC QTY]]="""",0,[@[PDS-ALC QTY]])" & Chr(10) & "   +IF([@[PDS-JOB QTY]]="""",0,[@[PDS-JOB QTY]])" & Chr(10) & "   +IF([@[PDS-GIT QTY]]="""",0,[@[PDS-GIT QTY]])," & Chr(10) & "R[-1]C)"
    Selection.AutoFill Destination:=Range("BG11" & ":BG" & NumRowsPDS)
majinvegito123
  • 25
  • 5