Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to calculate a Range from Row and Column count
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to calculate a Range from Row and Column count
Range(Cells(37, 3), Cells(37, intConsCount)).Select
Regards, Stefi €˛Terry€¯ ezt Ć*rta: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to calculate a Range from Row and Column count
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. "Terry" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to calculate a Range from Row and Column count
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to calculate a Range from Row and Column count
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up value range in column and then count | Excel Discussion (Misc queries) | |||
Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
How do I count a range of dates in a column? | Excel Worksheet Functions | |||
Get column count used range from S to IV | Excel Programming | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |