Extracting the Column and Row using VBA

Mister_B

New Member
Joined
Aug 28, 2002
Messages
15
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

voodoo

Board Regular
Joined
Apr 7, 2002
Messages
186
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

New Member
Joined
May 4, 2002
Messages
25
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

New Member
Joined
Aug 28, 2002
Messages
15
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
 

Forum statistics

Threads
1,147,621
Messages
5,742,180
Members
423,709
Latest member
AMacL

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top