Extracting the Column and Row using VBA

[Mister_B]

Folks,
Is there a way to take the cell address from the "activecell" and then assign a variable with the column name and do the same for the row name?

eg. say the active cell is K49 - I want to assign K to myVar1 and 49 to myVar2.

Can this be done, if so, how?

Thanks a bunch in advance

Dave

 

[voodoo]

Does the column have to be an alpha value?

The following will give you the column and row nunmbers but both numeric values
Sub CellCoords()
Dim x As String
Dim y As String

x = ActiveCell.Column
y = ActiveCell.Row
End Sub

 

[Ivan F Moala]

On 2002-08-29 21:51, Mister_B wrote:
Folks,
Is there a way to take the cell address from the "activecell" and then assign a variable with the column name and do the same for the row name?

eg. say the active cell is K49 - I want to assign K to myVar1 and 49 to myVar2.

Can this be done, if so, how?

Thanks a bunch in advance

Dave

Dave, yes this can be done but why do you want this ?
There may be an easier option for you if you explain why ?
Any way heres how;

<PRE><FONT color=blue>Sub </FONT>Tester()

<FONT color=blue>Dim </FONT>MyVar1

<FONT color=blue>Dim </FONT>MyVar2 <FONT color=blue>As</FONT><FONT color=blue> Double</FONT>





MyVar1 = Mid(ActiveCell.Address, InStr(ActiveCell.Address, "$") + 1, _

InStr(2, ActiveCell.Address, "$") - 2)

MyVar2 = ActiveCell.Row



MsgBox MyVar1 & MyVar2



<FONT color=blue>End Sub</FONT>


</PRE>

 

[Trevor Wood]

This works too if you are within the A-Z range..

myVar1 = Chr(ActiveCell.Column + 64)
myVar2 = ActiveCell.Row

Regards

Trevor
This message was edited by Trevor Wood on 2002-08-29 22:14

 

[Mister_B]

Thanks for the replies. Will try them and
see which works best for me.

I just joined this forum and totally
impressed with how speedily you folks
answered.

Truly appreciate your help

Dave