[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Referencing a Column with a Variable name

newguy

12/19/2006 1:58:00 AM

I have a variable (Columnname) that finds a column where a Cell in a
row equals a value on another page.(A user inserts a date on the first
page and it matches a value in a row on another page and this variable
is set to that column) The problem I am having now is using it to set
other cells.

Normally to set a cell on this page I would use
worksheetname.Range("B4").Value = something

But since the column is not going to be equal to "B" every time I need
to be able to call that variable Columnname so something like
worksheetname.Range.("Columnname" + 3).value = somthing
6 Answers

Dave Peterson

12/19/2006 2:12:00 AM

0

How about:

worksheetname.cells(4,columnname).value = something

(Don't you have to include the row, too?)


newguy wrote:
>
> I have a variable (Columnname) that finds a column where a Cell in a
> row equals a value on another page.(A user inserts a date on the first
> page and it matches a value in a row on another page and this variable
> is set to that column) The problem I am having now is using it to set
> other cells.
>
> Normally to set a cell on this page I would use
> worksheetname.Range("B4").Value = something
>
> But since the column is not going to be equal to "B" every time I need
> to be able to call that variable Columnname so something like
> worksheetname.Range.("Columnname" + 3).value = somthing

--

Dave Peterson

alok

12/19/2006 2:22:00 AM

0

You can do what Dave has suggested if ColumnName variable is an integer and
holds 1 for A and 2 for column B and so on.
On the other hand if ColumnName is a string variable holding "A","B" etc
then you can use a variation of what you have suggested

worksheetname.Range(ColumnName & 4).Value = something

Note that Worksheet name will have to be the programmatic name or else you
will need to use Worksheets(worksheetname).Range....

Alok

"newguy" wrote:

> I have a variable (Columnname) that finds a column where a Cell in a
> row equals a value on another page.(A user inserts a date on the first
> page and it matches a value in a row on another page and this variable
> is set to that column) The problem I am having now is using it to set
> other cells.
>
> Normally to set a cell on this page I would use
> worksheetname.Range("B4").Value = something
>
> But since the column is not going to be equal to "B" every time I need
> to be able to call that variable Columnname so something like
> worksheetname.Range.("Columnname" + 3).value = somthing
>
>

newguy

12/19/2006 2:58:00 AM

0

This is not working for me it is giving me a user defined error. I
have tried both what you have suggested as well as Dave. Since it is
the last worksheet in the workbook I have also tried.
Worksheet(worksheets.count).Range(Columname & 4.Value = something

And it still isnt working for me.


Thanks for your help



Alok wrote:
> You can do what Dave has suggested if ColumnName variable is an integer and
> holds 1 for A and 2 for column B and so on.
> On the other hand if ColumnName is a string variable holding "A","B" etc
> then you can use a variation of what you have suggested
>
> worksheetname.Range(ColumnName & 4).Value = something
>
> Note that Worksheet name will have to be the programmatic name or else you
> will need to use Worksheets(worksheetname).Range....
>
> Alok
>
> "newguy" wrote:
>
> > I have a variable (Columnname) that finds a column where a Cell in a
> > row equals a value on another page.(A user inserts a date on the first
> > page and it matches a value in a row on another page and this variable
> > is set to that column) The problem I am having now is using it to set
> > other cells.
> >
> > Normally to set a cell on this page I would use
> > worksheetname.Range("B4").Value = something
> >
> > But since the column is not going to be equal to "B" every time I need
> > to be able to call that variable Columnname so something like
> > worksheetname.Range.("Columnname" + 3).value = somthing
> >
> >

alok

12/19/2006 3:10:00 AM

0

Hi
Can you post the complete code from where the variable is declared to where
you get the error.
Alok

"newguy" wrote:

> This is not working for me it is giving me a user defined error. I
> have tried both what you have suggested as well as Dave. Since it is
> the last worksheet in the workbook I have also tried.
> Worksheet(worksheets.count).Range(Columname & 4.Value = something
>
> And it still isnt working for me.
>
>
> Thanks for your help
>
>
>
> Alok wrote:
> > You can do what Dave has suggested if ColumnName variable is an integer and
> > holds 1 for A and 2 for column B and so on.
> > On the other hand if ColumnName is a string variable holding "A","B" etc
> > then you can use a variation of what you have suggested
> >
> > worksheetname.Range(ColumnName & 4).Value = something
> >
> > Note that Worksheet name will have to be the programmatic name or else you
> > will need to use Worksheets(worksheetname).Range....
> >
> > Alok
> >
> > "newguy" wrote:
> >
> > > I have a variable (Columnname) that finds a column where a Cell in a
> > > row equals a value on another page.(A user inserts a date on the first
> > > page and it matches a value in a row on another page and this variable
> > > is set to that column) The problem I am having now is using it to set
> > > other cells.
> > >
> > > Normally to set a cell on this page I would use
> > > worksheetname.Range("B4").Value = something
> > >
> > > But since the column is not going to be equal to "B" every time I need
> > > to be able to call that variable Columnname so something like
> > > worksheetname.Range.("Columnname" + 3).value = somthing
> > >
> > >
>
>

Dave Peterson

12/19/2006 4:15:00 AM

0

It's better to copy|paste your code directly from the VBE.

Is this an error in your message or an error in your code:

Worksheet(worksheets.count).Range(Columname & 4).Value = something
(note the additional close paren before the .Value)

And note that this style:
worksheetname.cells(4,columnname).value = something
will work if columnname is a number or a letter
(as long as it is valid (1:255, or A:IV))


newguy wrote:
>
> This is not working for me it is giving me a user defined error. I
> have tried both what you have suggested as well as Dave. Since it is
> the last worksheet in the workbook I have also tried.
> Worksheet(worksheets.count).Range(Columname & 4.Value = something
>
> And it still isnt working for me.
>
> Thanks for your help
>
> Alok wrote:
> > You can do what Dave has suggested if ColumnName variable is an integer and
> > holds 1 for A and 2 for column B and so on.
> > On the other hand if ColumnName is a string variable holding "A","B" etc
> > then you can use a variation of what you have suggested
> >
> > worksheetname.Range(ColumnName & 4).Value = something
> >
> > Note that Worksheet name will have to be the programmatic name or else you
> > will need to use Worksheets(worksheetname).Range....
> >
> > Alok
> >
> > "newguy" wrote:
> >
> > > I have a variable (Columnname) that finds a column where a Cell in a
> > > row equals a value on another page.(A user inserts a date on the first
> > > page and it matches a value in a row on another page and this variable
> > > is set to that column) The problem I am having now is using it to set
> > > other cells.
> > >
> > > Normally to set a cell on this page I would use
> > > worksheetname.Range("B4").Value = something
> > >
> > > But since the column is not going to be equal to "B" every time I need
> > > to be able to call that variable Columnname so something like
> > > worksheetname.Range.("Columnname" + 3).value = somthing
> > >
> > >

--

Dave Peterson

alok

12/19/2006 12:38:00 PM

0

Dave,
A few days back I learnt something new. Today I learn one more. I did not
know that you can do Cells(4,"A").value. I apologise for implying in my
earlier post that this was not possible.
Thanks.
Alok

"Dave Peterson" wrote:

> It's better to copy|paste your code directly from the VBE.
>
> Is this an error in your message or an error in your code:
>
> Worksheet(worksheets.count).Range(Columname & 4).Value = something
> (note the additional close paren before the .Value)
>
> And note that this style:
> worksheetname.cells(4,columnname).value = something
> will work if columnname is a number or a letter
> (as long as it is valid (1:255, or A:IV))
>
>
> newguy wrote:
> >
> > This is not working for me it is giving me a user defined error. I
> > have tried both what you have suggested as well as Dave. Since it is
> > the last worksheet in the workbook I have also tried.
> > Worksheet(worksheets.count).Range(Columname & 4.Value = something
> >
> > And it still isnt working for me.
> >
> > Thanks for your help
> >
> > Alok wrote:
> > > You can do what Dave has suggested if ColumnName variable is an integer and
> > > holds 1 for A and 2 for column B and so on.
> > > On the other hand if ColumnName is a string variable holding "A","B" etc
> > > then you can use a variation of what you have suggested
> > >
> > > worksheetname.Range(ColumnName & 4).Value = something
> > >
> > > Note that Worksheet name will have to be the programmatic name or else you
> > > will need to use Worksheets(worksheetname).Range....
> > >
> > > Alok
> > >
> > > "newguy" wrote:
> > >
> > > > I have a variable (Columnname) that finds a column where a Cell in a
> > > > row equals a value on another page.(A user inserts a date on the first
> > > > page and it matches a value in a row on another page and this variable
> > > > is set to that column) The problem I am having now is using it to set
> > > > other cells.
> > > >
> > > > Normally to set a cell on this page I would use
> > > > worksheetname.Range("B4").Value = something
> > > >
> > > > But since the column is not going to be equal to "B" every time I need
> > > > to be able to call that variable Columnname so something like
> > > > worksheetname.Range.("Columnname" + 3).value = somthing
> > > >
> > > >
>
> --
>
> Dave Peterson
>