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

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

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

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

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

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