How to calculate a Range from Row and Column count

[Terry]

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

 

[Stefi]

Range(Cells(37, 3), Cells(37, intConsCount)).Select

Regards,
Stefi

„Terry†ezt írta:

 

[Joel]

Try something like this

rngStart01.Activate
Set rng = appExcel.ActiveCell.Offset( _
rowOffset:=7 + intSummaryLineCount, _
columnOffset:=2)
rng.FormulaR1C1 = "=SUM(R[" & (intSummaryLineCount * -1) & "]C:R[-1]C)"
set FillRange = Range(rng,rng.offset(0,intConsCount))
rng.AutoFill Destination:=FillRange, Type:=xlFillDefault
FillRange.NumberFormat = "$#,##0.00"



When using variable columns use cells(row,column) instead of Range("A1").
In this case I'm not sure if offset or cells is the better approach.

 

[Patrick Molloy]

first , you rarely have to select a range to use it

so NOT
Range("A1").Select
Selection = 2
instead
Range("A1") =2
or better
Range("A1").Value = 2 ' show the property, even if its the default, for
clarity

getting a range of nRows x nColumns

DIM target as Range
Set target = Range("A1").resize(nRows, nColumns)

if you want a row to replicate the formula of the first cell

With Range("A1").resize(1,nCFolumns)
.FormulaR1C1 = Range("A1").FormulaR1C1
End With

If your data has come back in a recordset, named rst, then

WITH Range("B5").Resize(rst.RecorCount, rst.Fields.Count)
.CopyFromRecordset rst
END WITH


hope these examples are useful

 

[Terry]

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