Find column name/number of a value in a row

S

Sinobato

Hi all,

I would like to find a certain value in a row, like row 7, and put th
column name or number on a variable. What is the script for doin
this?

thanks!
Sinobat
 
S

Sinobato

Tom,

I'm sorry if I was not clear. What I wanted to do is, let's say, I hav
several data on several columns but on a single row only, ie. A7, B7
C7, D7, E7, etc. Now, I would like to find a data along this row an
once found, get the column name/number so that I can use it later on m
script. Do I have to iterate through all the column, or can I do i
using the FIND command?

Thanks,
Sinobat
 
B

b&s

Sinobato > said:
Tom,

I'm sorry if I was not clear. What I wanted to do is, let's say, I
have several data on several columns but on a single row only, ie.
A7, B7, C7, D7, E7, etc. Now, I would like to find a data along this
row and once found, get the column name/number so that I can use it
later on my script. Do I have to iterate through all the column, or
can I do it using the FIND command?

Thanks,
Sinobato
Click to expand...


Just a suggestion:

ColNum = Application.Match("YourData", Rows("7:7"), 0)

--
regards/pozdrav!
Berislav

******************************************************************
ROT13 - email address [email protected]
 
T

Tom Ogilvy

Just to add
Sub Tester9()
Dim ColNum As Variant, Rng As Range
ColNum = Application.Match("YourData", Rows("7:7"), 0)
If Not IsError(ColNum) Then
Set Rng = Rows(7).Cells(1, ColNum)
MsgBox Rng.Address
Else
MsgBox "Not found"
End If
End Sub


or using Find:

Sub Tester10()
Dim ColNum As Long, Rng As Range
Set Rng = Rows(7).Find("YourData")
If Not Rng Is Nothing Then
ColNum = Rng.Column
MsgBox Rng.Address & " Column: " & ColNum
Else
MsgBox "Not found"
End If
End Sub
 
S

Sinobato

All,

Thanks for your replies! This forum is such a hit with people lik
you!

Kind regards,
Sinobat
 
Top