Martin Fishlock
12/15/2006 12:45:00 PM
Kevin
Here is another option:
Option Explicit
Function FindRowRevised(Column1 As Integer, TextToFind As String)
Dim c As Range
With ActiveSheet.Columns(Column1)
Set c = .Find(What:=TextToFind, LookIn:=xlValues, LookAt:=xlWhole)
If c Is Nothing Then
FindRowRevised = 0
Else
FindRowRevised = c.Row
End If
End With
End Function
If you decide to use Ankur's suggestion then the i should be a long. Also
with 2007 explicitly declaring 65536 is not good and it may be more advisable
to use cells.rows.count
Function FindRow(Column1 As Integer, TextToFind As String)
Dim i as integer
Dim cell As Object
For i = 1 To 65536
RowOfText = 0
If Cells(i, Column1).Value <> "" Then
If Cells(i, Column1).Value = TextToFind Then
RowOfText = i
GoTo 8
End If
End If
Next i
8: If RowOfText = 0 Then
FindRow = 0
Else
FindRow = RowOfText
End If
End Function
Regards
/ Kanchan
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.
"Kc-Mass" wrote:
> I am good with access new with Excel
>
> What I want to do, in VBA, is search Column B and get the row number
> for the first occurence of a value. Let's say I am searching for a value of
> "Fish" and "Fish" is in B1207. I would just like the Function to give back
> the row number 1207.
>
> Something Like:
>
> Function FindRow ( Column, TextToFind)
>
> Code
> Code
> Findrow = X
> End Function
>
> A. Is it possible.
>
> B. Could you give me an idea of what I am looking for and where I
> should look.
>
> Thx
>
> Kevin C
>
>
>