Dynamically Assigning a Range

[Privateer59]

I am trying to assign a range to a variable. The row number (TRN) is 17 and the column number (TCN) is 10. I would like to assign the variable current sheet range (CSR), dimmed as a range, to this:

Row 18 Column 5 through Row 18 Column 10 which equals "E18:J18"

Set CSR = xlSheet.Range(Cells(" & (TRN + 1) & ", " & (TCN - 5) & "), Cells(" & (TRN + 1) & ", " & TCN & "))

The error is "1004 Method 'Cells' of object '_Global' failed", whatever that means.

What I was hoping for is this: Set CSR = xlSheet.Range(Cells(18, 5), Cells(18, 10))

I have to do this dynamically as "J17" is the control-end cell (last cell) in the sheet and the next time the data is exported from Access, the last row will be different. I would like the range assigned to the variable CSR so I can assign a formula and do some formatting. Been messing with this for a while and I am really frustrated. Just want to sum five columns of numbers without knowing where the bottom is each time. Any help would be greatly appreciated.

Privateer59

p.s. if you are interested:

TRN = xlSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
TCN = xlSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

 

[sericom]

Try

Set CSR = xlSheet.Range(Cells(TRN + 1, TCN - 5), Cells(TRN + 1, TCN))

 

[Aaron Blood]

Not that there is anything wrong with seri's suggested code...

Just pointing out that there are range manipulation methods to do things like this:

Set CSR = xlSheet.UsedRange.End(xlDown).Resize(1, 5).Offset(1, 0)

...which eliminates the need for ever having to set the TRN, TCN variables. Granted, there are also ways to adjust your approach if you have to deal with empty cells in the data batch. Just wanted to point out the Resize and Offset range methods.

 

[Aaron Blood]

This construct would avoid the previously mentioned empty cell issues...

Set CSR = xlSheet.UsedRange
Set CSR = CSR.Offset(CSR.Rows.Count, 0).Resize(1, CSR.Columns.Count)

...sorry wouldn't let me edit my last post, I was too slow.

 

[Privateer59]

Sericom, I grabbed your response first and it worked. Thank you. I guess I was overthinking the concatenation. It was the one line of code holding up the whole project. Aarron, I appreciated the assist, have not tried it yet but I copied the code into the module and will try it later. It is always good to have multiple ways of doing things. The reason I wanted the rows and columns was the creation of a formula in the last row plus one. I post it here so others can see it but also to see if there is a better way to create a sum formula when the bottom row is unknown. Sericom, per your post, I tried to remove the ampersands and parentheses but it failed, perhaps there is too much math in this one.

CSR.FormulaR1C1 = "=SUM(R[" & ((TRN * -1) + 1) & "]C:R[-1]C)"

Using the example in the initial post and with the range CSR now set at E18:J18, the resultant formula =sum(E2:E17) is entered into E18 through J18 with the columns adjusting automatically. It is exactly what I wanted, a dynamic totaling formula at the bottom of the five columns. Thank you both for the help.
Privateer59