Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Column of Named Cell vba
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Column of Named Cell vba
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Column of Named Cell vba
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Column of Named Cell vba
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Column of Named Cell vba
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Column of Named Cell vba
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet won't reference a named cell | Excel Worksheet Functions | |||
HOWTO Reference a named cell or range in a script | Excel Discussion (Misc queries) | |||
Vlookup - using a cell to reference a named array | Excel Worksheet Functions | |||
reference first cell in a named range | Excel Worksheet Functions | |||
Named Range reference via single Cell | Excel Discussion (Misc queries) |