Caontains.

[Tom]

Hello again,

how can I check it if hole column contains string which I am lookig for.

for example:
mystring = "test2005"
if mystring in column A give me number of this row

thanks tomas

 

[Harald Staff]

Hi THomas

One way, returns 0 for no match:

Sub test()
MsgBox FoundRow(Range("A:A"), "test2005")
End Sub

Function FoundRow(Rng As Range, Txt As String) As Long
On Error Resume Next
FoundRow = Rng.Find(What:=Txt, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False).Row
End Function

HTH. Best wishes Harald

 

[Tom Ogilvy]

set rng = columns(1).Find("test2005")
if not rng is nothing then
msgbox "found at row " & rng.row
End if

Assumes the test2005 would be the hard entered value in the cell and not
part of a larger string or produced by a formula.

 

[Guest]

A mechanical solution that checks an input range is as follows.

Function Row_Match(Ip_Range As range, MatchCell As range) As Integer
Dim i As Integer, j As Integer

For j = 1 To Ip_Range.Columns.count
For i = 1 To Ip_Range.Rows.count
If Ip_Range.Cells(i, j) = MatchCell.Value Then
Row_Match = i
Exit For
End If
Next i
Next j

End Function

Ip_Range is the range to be checked. MatchCell is the cell that contains
what you want to find in the range.