PDA

View Full Version : [SOLVED] Range Address



mdmackillop
05-08-2005, 01:21 PM
Is there a simple method to return the address of the first cell of a named range ie, without having to do a string manipulation?

Bob Phillips
05-08-2005, 01:22 PM
Range("myRange").Cells(1,1).Address

mdmackillop
05-08-2005, 01:46 PM
Thanks.

brettdj
05-08-2005, 08:14 PM
Malcom,

I normally use relative references to avoid returng the "$", ie



MsgBox Range("A1:A10").Cells(1).Address(False, False)


Using True, False is a handy way to split the cell address to return the column, ie



Dim AC
'Split requires Excel 2000
AC = Split(Range("A1:A10").Cells(1).Address(, False), "$")
MsgBox "Column is " & AC(0)
End Sub

Cheers

Dave

mdmackillop
05-09-2005, 11:01 AM
Thanks for these Dave. I'll file them away for future use.

Cyberdude
05-09-2005, 02:23 PM
For what it's worth, the SPLIT function appears to be available in Excel 2003, at least I have it.

Zack Barresse
05-09-2005, 02:28 PM
For what it's worth, the SPLIT function appears to be available in Excel 2003, at least I have it.
Don't believe it was in 97; should be there after that. Not sure about 2000 though.


xld: Nice. :thumb I usually just shorten to Range(1).Address, but you can always specify the column.

johnske
05-09-2005, 02:47 PM
Don't believe it was in 97; should be there after that. Not sure about 2000 though....

Hi Zack,

Yes, it's in 2k

John

brettdj
05-09-2005, 02:50 PM
Maybe I should have specified that Split requires a minimum of Excel 2000 instead :)

mdmackillop
05-09-2005, 02:54 PM
Thanks all.
To sum up, I've now got:

Add = MyRange(1).Address(0, 0)
Rw = MyRange(1).Row()
Col = Split(MyRange(1).Address(1, 0), "$")(0)

Zack Barresse
05-09-2005, 02:58 PM
Why not just ...

Rw = MyRange(1).Row
Col = MyRange(1).Column

johnske
05-09-2005, 02:59 PM
BTW Dave, having only fairly recently migrated from 97 to 2k, I simply wasn't aware that split even existed - very nyce

Thanx,
John :)

Bob Phillips
05-09-2005, 03:01 PM
Maybe I should have specified that Split requires a minimum of Excel 2000 instead

Can be emulated using conditional compilation so as to use the built-in if there, else use the custom version



#If VBA6 Then
#Else

Function Split(Text As String, _
Optional Delimiter As String = ",") As Variant
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues
If Delimiter = vbNullChar Then
Delimiter = Chr(7)
Text = Replace(Text, vbNullChar, Delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") & """}"
aryEval = Evaluate(sFormula)
ReDim aryValues(0 To UBound(aryEval) - 1)
For i = 0 To UBound(aryValues)
aryValues(i) = aryEval(i + 1)
Next
Split = aryValues
End Function
#End If

Bob Phillips
05-09-2005, 03:03 PM
BTW Dave, having only fairly recently migrated from 97 to 2k, I simply wasn't aware that split even existed

And InstrRev

mdmackillop
05-09-2005, 03:05 PM
Hi Zack,
I was going for the column letter here, trying to get Dave's solution on one line, as the column number was apparent from the row solution.

Zack Barresse
05-09-2005, 03:12 PM
MD: Gotcha! :)

xld: Good point about the InStrRev! That one gets a lot of people, surprisingly (to me anyway). Also, that Split looks like a great KB entry! :yes

Bob Phillips
05-09-2005, 03:18 PM
Also, that Split looks like a great KB entry!

I've got a 97 InstrRev as well if anyone wants to create it.

Zack Barresse
05-09-2005, 03:20 PM
I've got a 97 InstrRev as well if anyone wants to create it.
The KB isn't going anywhere. ;)

But if you post it in the Potential KBase forum, someone may be able to pick it up. I'm sure Johnske or some of the others wouldn't mind with the Kbase contest and all. :yes

mdmackillop
05-09-2005, 03:24 PM
or:

Add = MyRange(1).Address
Rw = MyRange(1).Row()
Col = Split(Add, "$", -1)(1)

johnske
05-09-2005, 03:52 PM
.... Also, that Split looks like a great KB entry! :yes

Dave, gotta agree with Zack on that - Split would make a great mini-entry. :thumb

After all, one of the main reasons for VBAX's existence is to pass on the old and pick up the new things that we've learnt with VBA...

Regards,
John

Zack Barresse
05-09-2005, 03:54 PM
.. Split would make a great mini-entry. :thumb ..
Also, because a lot of users still use 97, or lord forbid even 95! :wot

brettdj
05-09-2005, 04:57 PM
I'd use a simple InStr would suffice for 97 rather than Split. Thats why I used the True, False approach to return the address as it only returns a "$" in between column and row. Hence no need for InStrRev.

Split, InStr and InStrRev are all handy functions but for serious string manipulation a Regular Rxpression wins hands down. I'm going to convert you all to the cause :)

John, you might want to have a look at http://j-walk.com/ss/excel/tips/tip93.htm, Walkenbach had a nice write-up on it.

Cheers

Dave

johnske
05-09-2005, 05:33 PM
Split, InStr and InStrRev are all handy functions but for serious string manipulation a Regular Rxpression wins hands down. I'm going to convert you all to the cause :)

John, you might want to have a look at http://j-walk.com/ss/excel/tips/tip93.htm, Walkenbach had a nice write-up on it.

Cheers

Dave

Thanx Dave, haven't seen that page (perhaps cos I wasn't looking for it). I still think that a Split* example or two in the kb would be handy for those of us that prefer to use more 'simple' solutions and will leave RegExp for when we really have no other simpler option :devil:

Regards,
John

*Of course the entry'd have to note that it only applies to 2000 and up

brettdj
05-09-2005, 05:39 PM
Ok, I'll write one up

Maybe comparing the three techniques

Cheers

Dave

Zack Barresse
05-09-2005, 05:46 PM
I'm going to convert you all to the cause :)

That's the spirit!!! :D