Thanks for all the help, there is a lot of useful info there for me as a
'first timer' using automation from Access 2007 to Excel 2007. I had a
report in Access that used a number of crosstabs to get what I needed but
Access just couldn't hack it taking about 2 minutes to run and with poor
reliability. Running a sub in Access with recordsets and passing data to
Excel takes about 5 seconds, way to go!

I used this in the end:

Set rngStart02 = Range(rng, rng.Offset(0, (intConsCounted + 1)))
rng.AutoFill Destination:=rngStart02, Type:=xlFillDefault
rngStart02.NumberFormat = "$#,##0.00"

Thanks Guys


"Terry" wrote in message
...
I am filling a range with data from Access queries where one of the queries
is a crosstab. The crosstab may produce from 1 to 6 columns and a variable
number of rows. I know how many columns and rows the crosstab produces and
I use those counts to help me point to the first column to be summed which
I do with the following:

rngStart01.Activate
Set rng = appExcel.ActiveCell.Offset(rowOffset:=7 +
intSummaryLineCount, columnOffset:=2)
rng.FormulaR1C1 = "=SUM(R[" & (intSummaryLineCount * -1) & "]C:R[-1]C)"

intSummaryLineCount is the number of rows and intConsCount is the number
of columns being returned from the crosstab. I would like to copy the
formula in the first cell pointed to across a calculated number of
columns.

The recorded macro on a sample sheet gave me the following:

Range("C37").Select ' this is the cell pointed to by Set rng in
above code
Selection.AutoFill Destination:=Range("C37:G37"), Type:=xlFillDefault
Range("C37:G37").Select ' G37 needs to be calculated using
intConsCount
Selection.NumberFormat = "$#,##0.00"

The problems I have is 1) the correct syntax 2) how to use the column
count within the Selection.and Range().Select

I have the following variables set:

Dim appExcel As Excel.Application
Dim bks As Excel.Workbooks
Dim rng As Excel.Range
Dim rngStart01 As Excel.Range
Dim wkb As Excel.Workbook
Dim sht1 As Excel.Worksheet

Set sht1 = appExcel.ActiveWorkbook.Sheets(1)
Set rngStart01 = sht1.Range("A6")
Set rng = appExcel.ActiveCell.Offset(rowOffset:=X, columnOffset:=X) to
move around worksheet