Reference Column of Named Cell vba

  • Thread starter Thread starter Isis
  • Start date Start date
I

Isis

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
 
Dim myCell as range
set mycell = worksheets("somesheetname").range("SomeRangeName")

Then you can use something like:

msgbox mycell.column
Click to expand...

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
 
msgbox mycell.column
Click to expand...

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
 
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.)
 
Back
Top