How to return the row number or column letter as a variable?
 

Hi,

I have a sheet with measurement data.
The first row contains the headers waith the parameter name.
I have to look to a specific data from one parameter and find the data from
another parameter in the same row.
Unfortunately the order of the parameters is not always the same.

I want to solve this by searching for the first parameter (stapp) and set a
variable (col_stapp) equal to the letter of the column of that parameter.
I tried the following code:
Dim col_stapp as String
Cells.find(What:="stapp", After:=ActiveCell, LookIn:=xlValue,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearcgDirection:=xlNext,
MatchCase:=False).Activate
col_stapp = Cells.Column

But I always get for col_stapp the value 1.

Whats wrong and how can I solve this?

Thanks for your help,

Gerrit

How to return the row number or column letter as a variable?
 

have not tryed your code but try
col_stapp = activeCell.Column
instead of
col_stapp = Cells.Column
in the last line of your code


"cells" referes to all cells in the work sheet so that is
why its returning 1

-----Original Message-----
Hi,

I have a sheet with measurement data.
The first row contains the headers waith the parameter
name.
I have to look to a specific data from one parameter and
find the data from
another parameter in the same row.
Unfortunately the order of the parameters is not always
the same.

I want to solve this by searching for the first
parameter (stapp) and set a
variable (col_stapp) equal to the letter of the column
of that parameter.
I tried the following code:
Dim col_stapp as String
Cells.find(What:="stapp", After:=ActiveCell,
LookIn:=xlValue,
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearcgDirection:=xlNext,
MatchCase:=False).Activate
col_stapp = Cells.Column

But I always get for col_stapp the value 1.

Whats wrong and how can I solve this?

Thanks for your help,

Gerrit


.

How to return the row number or column letter as a variable?
 

Dim col_stapp as String
Cells.find(What:="stapp", After:=ActiveCell, LookIn:=xlValue,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearcgDirection:=xlNext,
MatchCase:=False).Activate
col_stapp = ActiveCell.Column


--
Regards,
Tom Ogilvy

"Gerrit van Stempvoort" wrote in message
...
Hi,

I have a sheet with measurement data.
The first row contains the headers waith the parameter name.
I have to look to a specific data from one parameter and find the data
from
another parameter in the same row.
Unfortunately the order of the parameters is not always the same.

I want to solve this by searching for the first parameter (stapp) and set
a
variable (col_stapp) equal to the letter of the column of that parameter.
I tried the following code:
Dim col_stapp as String
Cells.find(What:="stapp", After:=ActiveCell, LookIn:=xlValue,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearcgDirection:=xlNext,
MatchCase:=False).Activate
col_stapp = Cells.Column

But I always get for col_stapp the value 1.

Whats wrong and how can I solve this?

Thanks for your help,

Gerrit

How to return the row number or column letter as a variable?
 

There's no need to activate the cell before assigning to the variable:

Dim col_stapp As String
col_stapp = Cells.Find(What:="stapp", After:=ActiveCell, _
LookIn:=xlValue, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Column

Alan Beban

Tom Ogilvy wrote:
Dim col_stapp as String
Cells.find(What:="stapp", After:=ActiveCell, LookIn:=xlValue,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearcgDirection:=xlNext,
MatchCase:=False).Activate
col_stapp = ActiveCell.Column

How to return the row number or column letter as a variable?
 

Hi Guy's

Thanks for your help.
Alan, your right, it is not needed to activate the cell before assigning to
the variable, but because I had to search in that column for a specific
value is is easier to activate the cell. So I will use the solution from
Tom.

Gerrit van Stempvoort

"Alan Beban" wrote in message
...
There's no need to activate the cell before assigning to the variable:

Dim col_stapp As String
col_stapp = Cells.Find(What:="stapp", After:=ActiveCell, _
LookIn:=xlValue, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Column

Alan Beban

Tom Ogilvy wrote:
Dim col_stapp as String
Cells.find(What:="stapp", After:=ActiveCell, LookIn:=xlValue,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearcgDirection:=xlNext,
MatchCase:=False).Activate
col_stapp = ActiveCell.Column