Range returns unexpected value.

[AlexanderBB]

In Module1
Public iRange As Range

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Row > 1 Then
    Cancel = True
    Set iRange = Range("A" & Target.Row & ":CV" & Target.Row)
end if

Debug.print iRange(Target.Row, "C")
Target.Row is 5534.

I expect to see the value of Col C at row 5534 but instead it shows the Contents of "C" at row 11070
Excel must be right and I'm wrong, but how ?

 

[Phuoc]

Debug.print Range("C" & Target.Row). Value

 

[AlexanderBB]

OK, but what about the range issue ?

 

[Phuoc]

Debug.print iRange (1, 3). Value

 

[AlexanderBB]

Ah, got it. Thanks... although ? iRange(2,3) instead of an error prints Col C for Target.row+1
I would have thought Set iRange = Range("A" & Target.Row & ":CV" & Target.Row) would limit the range to just one row.
The idea being that range could be used anywhere and contain the data in that row.

 

[Alex Blakenburg]

I'm not sure how to explain it without making it more confusing.
I am suprised you say it returned row 11070 it should have been $C$11067.

? Range("A5534:CV5534").Address
returns $A$5534:$CV$5534

But you are using
? iRange(5534,"C").Address (you could use 3 instead of C)
which should have returned
$C$11067

Since iRange = Range("A5534:CV5534"), you have effectively put
? Range("A5534:CV5534")(5534,"C").Address

Where
? Range("A5534:CV5534")(1,"A").Address
$A$5534
? Range("A5534:CV5534")(1,"C").Address
$C$5534
? Range("A5534:CV5534")(2,"A").Address
$A$5535

Further reading: Range.Item property (Excel)

 

[AlexanderBB]

Yes, you're right it was $C$11067. and it is a bit confusing.
First time I'd used Range like this and I had it all wrong, but seemed logical.
But right result with iRange (1, Col) instead of (LineNum, Col).
And I presume no way without the "1"?

 

[Alex Blakenburg]

If you want to describe what you are trying to do we might be able to give you some other suggestion but in terms of your last question this might work.
Row position left blank but you still need the comma before the C
? Range("A5534:CV5534")(,"C").Address
$C$5534
or in your case
? iRange(,"C").address
$C$5534

 

[AlexanderBB]

Ah,OK. I'll use that, thanks.
I'm doing stuff in VBA with data in a certain row, I send In Target to my functions but that didn't (easily) give any column so trying this
global Range object which looks like it'll do what I want. I have every column on tap now.

 

[Alex Blakenburg]

There should be no issue sending the Target to a function.