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

  • Thread starter Gerrit van Stempvoort
  • Start date
G

Gerrit van Stempvoort

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
 
S

steve

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
 
T

Tom Ogilvy

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
 
A

Alan Beban

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
 
G

Gerrit van Stempvoort

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
 
Top