Prev: VBA vocabulary
Next: convert .123 file
From: Isis on 3 May 2010 07:40
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
From: Dave Peterson on 3 May 2010 08:04
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
From: Isis on 3 May 2010 08:24
Dave Peterson <petersod(a)verizonXSPAM.net> wrote in
news:4BDEBBBD.B7AF3490(a)verizonXSPAM.net:

> 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
From: Isis on 3 May 2010 08:33
Dave Peterson <petersod(a)verizonXSPAM.net> 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
From: Dave Peterson on 3 May 2010 08:35
Dim iColumn as long
icolumn = worksheets("somesheetname").range("StaffHeader1").column

Isis wrote:
>
> Dave Peterson <petersod(a)verizonXSPAM.net> wrote in
> news:4BDEBBBD.B7AF3490(a)verizonXSPAM.net:
>
> > 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
 |  Next  |  Last
Pages: 1 2
Prev: VBA vocabulary
Next: convert .123 file