Find column name/number of a value in a row

[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

 

[Tom Ogilvy]

vRowNum = 7

 

[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&s]

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

Just a suggestion:

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

--
regards/pozdrav!
Berislav

******************************************************************
ROT13 - email address (e-mail address removed)

 

[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

 

[Sinobato]

All,

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

Kind regards,
Sinobat