Reference Column of Named Cell vba

I have named a cell on sheet - how doe I reference just the column property
of that named cell in vba on another sheet in the same workbook please ?


Thanks

Dim myCell as range
set mycell = worksheets("somesheetname").range("SomeRangeName")

Then you can use something like:

msgbox mycell.column



Isis wrote:

I have named a cell on sheet - how doe I reference just the column property
of that named cell in vba on another sheet in the same workbook please ?

Thanks

--

Dave Peterson

Dave Peterson wrote in
:

Dim myCell as range
set mycell = worksheets("somesheetname").range("SomeRangeName")

Then you can use something like:

msgbox mycell.column



Isis wrote:

I have named a cell on sheet - how doe I reference just the column
property of that named cell in vba on another sheet in the same
workbook please ?

Thanks


Hi Dave - thanks for the reply - that certainly looks like it does the job
but....

Is there no way to directly extract the column something like this (pseudo
code);

iColumn = Column(StaffHeader1)
Sheet6.Cells(3, iColumn + 1).Value = iStaffName

not complaining as your way will work, just a lot of code !

Thanks for taking the time to answer - appreciated

Regards

Dave Peterson wrote in news:4BDEBBBD.B7AF3490
@verizonXSPAM.net:

msgbox mycell.column



Dave,

I am obviously doing something wrong as this code;

Dim myCell As Range
Set myCell = Worksheets("Sheet4").Range("StaffHeader1")
MsgBox myCell.Column

gives me a "subscript out of range error"

Any ideas ?

Thanks

PS I have a Sheet4 and StaffHeader1 is a named cell on that sheet

Dim iColumn as long
icolumn = worksheets("somesheetname").range("StaffHeader1"). column

Isis wrote:

Dave Peterson wrote in
:

Dim myCell as range
set mycell = worksheets("somesheetname").range("SomeRangeName")

Then you can use something like:

msgbox mycell.column



Isis wrote:

I have named a cell on sheet - how doe I reference just the column
property of that named cell in vba on another sheet in the same
workbook please ?

Thanks


Hi Dave - thanks for the reply - that certainly looks like it does the job
but....

Is there no way to directly extract the column something like this (pseudo
code);

iColumn = Column(StaffHeader1)
Sheet6.Cells(3, iColumn + 1).Value = iStaffName

not complaining as your way will work, just a lot of code !

Thanks for taking the time to answer - appreciated

Regards

--

Dave Peterson

Either your names aren't what you think they are (a typo or two???).

Or maybe Sheet4 is the CodeName of the sheet--not the name the user sees on the
sheet tab at the bottom of the screen in Excel????

Set myCell = Sheet4.Range("StaffHeader1")

(Check to see if that last character in staffheader? is really a one or an ell,
too.)

Isis wrote:

Dave Peterson wrote in news:4BDEBBBD.B7AF3490
@verizonXSPAM.net:

msgbox mycell.column



Dave,

I am obviously doing something wrong as this code;

Dim myCell As Range
Set myCell = Worksheets("Sheet4").Range("StaffHeader1")
MsgBox myCell.Column

gives me a "subscript out of range error"

Any ideas ?

Thanks

PS I have a Sheet4 and StaffHeader1 is a named cell on that sheet

--

Dave Peterson

Try?

Sub whichcolofnamedrng()
MsgBox Range("mydefinedname").Column
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Isis" wrote in message
...
I have named a cell on sheet - how doe I reference just the column property
of that named cell in vba on another sheet in the same workbook please ?


Thanks