XL VBA Range selection 1

[RivetHed]

Hi all,

having a little problem with a range selection, the following line:

RngData.Range(RngData.Cells(2, 4), RngData.Cells(RngData.Rows.Count, 4)). _

FormulaR1C1 = "=SUM(RC[+1]:RC[+" & RngData.Columns.Count - 4 & "])"

is inserting the formula into a range offset 2 rows from where it should so it starts 3 rows below the header row instead of 1.

I have tested a bit further and this line:

RngData.Cells(2, 4).Select

selects a different cell to this line:

RngData.Range(RngData.Cells(2, 4), RngData.Cells(2, 4)).Select

I assume I've just forgotten something basic as it's been a while since I last coded in XL.

Thanks

Alex

 

[TonyJollans]

Hi RivetHed,

I wouldn't say it something basic exactly, but it is something that's a bit awkward to get your head round.

[tt] RngData.Range(RngData.Cells(2, 4), RngData.Cells(2, 4)).Select[/tt]

is double-qualified.

[tt]RngData.Range(...[/tt] is saying select the range within RngData which is addressed by the starting and ending cells you give.

The starting and ending cells you have given are offsets from the start of RngData ([tt]RngData.Cells(...[/tt]). They are resolved against the worksheet before being applied to the Range to select.

The equivalent of ..

[tt] RngData.Cells(2, 4).Select[/tt]

is, more simply:

[tt] Range(RngData.Cells(2, 4), RngData.Cells(2, 4)).Select[/tt]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[RivetHed]

Thanks Tony, had to read that through a couple of times but I get what you're saying.