Copy formula to a column of cells

[Guest]

Cells A8 to G (whatever) are filled with an MS query, that is refreshed by
pressing a VBA button.
As well as refreshing the data the button also creates a subtotal of the
cells, breaking it by the first column (user name) and summing the last three
columns.

One thing I can't put into the spreadsheet from the query is a formula, so
this is also added in to column H when the button is pressed, as follows:-

Private Sub CommandButton1_Click()
Range("A8").select
Selection.subtotal Group:=1,
Funtion:=xlSum,TotalList:=Array(5,6,7), _

Replace:=True,PageBreaks:=False,SummaryBelowData:=True
Range("H8:H8").cells(1,1).Formula ="=IF(D="""",E8/((G8-INT(G8))*24),"""")"
Range("H8").Select
Selection.End(xlDown).Select
Selection.Autofill Destination:=Range("H8:H2000")
Range("H8LH2000").Select
Range("H8").Select
End Sub

The above code works but it fills from H8 to H2000 each time the query is
run. I want it to fill to the end of the data, if the data finishes at 200,
the it fills H8:H200, if the data finishes at G20000 then the formula is
filled H8:H20000...
Etc.
Etc.

Help please. I'm not a programmer. so all help will have to be detailed.

Thanks
Dean

 

[Jim Cone]

Dean,
Are you sure that the code you posted worked?
In any case, give the following a try...
'-----------------------
Private Sub CommandButton1_Click()
Dim rngLastCell As Excel.Range

Range("A8").Subtotal Groupby:=1, Function:=xlSum, TotalList:=Array(5, 6, 7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("H8").Formula = "=IF(D="""",E8/((G8-INT(G8))*24),"""")"
Range("H8").End(xlDown).Select
Set rngLastCell = Cells(Rows.Count, 8).End(xlUp)
Range("H8").AutoFill Destination:=Range("H8", rngLastCell)
Range("H8").Select
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Dean" <[email protected]>
wrote in message
Cells A8 to G (whatever) are filled with an MS query, that is refreshed by
pressing a VBA button.
As well as refreshing the data the button also creates a subtotal of the
cells, breaking it by the first column (user name) and summing the last three
columns.

One thing I can't put into the spreadsheet from the query is a formula, so
this is also added in to column H when the button is pressed, as follows:-

Private Sub CommandButton1_Click()
Range("A8").select
Selection.subtotal Group:=1,
Funtion:=xlSum,TotalList:=Array(5,6,7), _

Replace:=True,PageBreaks:=False,SummaryBelowData:=True
Range("H8:H8").cells(1,1).Formula ="=IF(D="""",E8/((G8-INT(G8))*24),"""")"
Range("H8").Select
Selection.End(xlDown).Select
Selection.Autofill Destination:=Range("H8:H2000")
Range("H8LH2000").Select
Range("H8").Select
End Sub

The above code works but it fills from H8 to H2000 each time the query is
run. I want it to fill to the end of the data, if the data finishes at 200,
the it fills H8:H200, if the data finishes at G20000 then the formula is
filled H8:H20000...
Etc.
Etc.
Help please. I'm not a programmer. so all help will have to be detailed.
Thanks
Dean