I have managed to get all the information from a query into an excel
spreassheet using VBA. I am not sure how to set the range of a recordset or
how to provide columns on the side of the report to calculate based on the #
of records. For example, I have all the records (a:h) and in column (i) I
need to calculate (a+b).
How do I do this with VBA. The number of records from the query will
fluctuate every time the report is ran.
How do I set the function in a blank cloumn based on how many records there
are?

Either add this code to your routine that gets the information in VBA or
create a new Sub and use this code in it.

You'll need to change the Range("A1").Select to choose the first cell in
column A where your data starts. It is set up now to build the formulas in
column I.

Dim LoopCounter As Long
'select first cell with something to add in it
'that is in column A
Range("A1").Select
If IsEmpty(ActiveCell) Then
Exit Sub
End If
'change 8 to column # you want formula in
'C = 3, D= 4, ... I =8, J=9, etc.
'special case - chose last in list
If IsEmpty(ActiveCell.Offset(1, 0)) Then
ActiveCell.Offset(0, 8).Formula _
= "=A" & ActiveCell.Row & "+B" & ActiveCell.Row
Exit Sub
End If
For LoopCounter = ActiveCell.Row To _
ActiveCell.End(xlDown).Row
'change all 8s to column # you want formula in
'C = 3, D= 4, ... I =8, J=9, etc.
ActiveCell.Offset(LoopCounter - ActiveCell.Row, 8).Formula _
= "=A" & ActiveCell.Offset(LoopCounter - ActiveCell.Row, 8).Row _
& "+B" & ActiveCell.Offset(LoopCounter - ActiveCell.Row, 8).Row
Next

Hope you find this helpful.
"marthasanchez" wrote:

I have managed to get all the information from a query into an excel
spreassheet using VBA. I am not sure how to set the range of a recordset or
how to provide columns on the side of the report to calculate based on the #
of records. For example, I have all the records (a:h) and in column (i) I
need to calculate (a+b).
How do I do this with VBA. The number of records from the query will
fluctuate every time the report is ran.
How do I set the function in a blank cloumn based on how many records there
are?

Search the help file for topic CopyFromRecordset; it might be what you are
looking for.
Why not calculate (a+b) as another column in your SQL?