Find in a column

[Francis Hookham]

In a subroutine I need to find which row where a specific numeral occurs in
a column A. numbers will start in A4 and there may be blank cells in the
column.



The Module already has the following

LastUsedRow = Cells(Rows.Count, 1).End(xlUp).Row

to find the range:

Range(cells(4,1),cells(LastUsedRow,1))



How do I carry on from there to find NumberRow?



Thanks



Francis Hookham

 

[Norman Jones]

Hi Francis,

Try something like:

'============>>
Dim RngFound As Range
Const sSearchString As String = 44 '<<==== CHANGE

Set RngFound = rng.Find(What:=sSearchString, _
After:=rng.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not RngFound Is Nothing Then
'do something, e.g:
MsgBox RngFound.Row
End If
'<<============

 

[Guest]

Adding to Norm's response:

Dim RngFound As Range
Const sSearchString As String = 44 '<<==== CHANGE
With Range(cells(4,1),cells(LastUsedRow,1))
Set RngFound = rng.Find(What:=sSearchString, _
After:=rng.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not RngFound Is Nothing Then
'do something, e.g:
MsgBox RngFound.Row
End If
End With

Just to make sure you specifiy the range to search.

 

[Guest]

Disregard my previous post. I see that Norm use rng as a variable for your
defined range, so you don't need the With statement. Just be sure to assign
the variable:

Set rng = Range(cells(4,1),cells(LastUsedRow,1))

 

[Francis Hookham]

Thanks Norman for the TextBox deletion answer - it was just what I wanted

This problem just shows up how little I know - I do not know how to make
your suggestion work. It's late and I'm old and tired and have tried
unsuccessfully to set out below a Sub which would crudley do what I am
trying to do - it does not work but it might show what I want - please!

Thanks
Francis

Sub FindRowNum()
Sheets("Specs").Select
'find extent of col 1
LastUsedRow = Cells(Rows.Count, 1).End(xlUp).Row
'start in row 2
Cells(2, 1).Select
'number to be found
NumToFind = InputBox("Find which number?")
'loop to stop at LastUsedRow
While ActiveCell.Row <= LastUsedRow
'loop to step down column
While ActiveCell.Value < NumToFind
ActiveCell.Offset(1, 0).Select
Wend
RowNum = ActiveCell.Row
Wend
End Sub

 

[Norman Jones]

Hi Francis,

'---------------
This problem just shows up how little I know - I do not know how to make
your suggestion work. It's late and I'm old and tired and have tried
unsuccessfully to set out below a Sub which would crudley do what I am
trying to do - it does not work but it might show what I want - please
'---------------

Try the following version:

'=============>>
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim RngFound As Range
Dim iLastRow As Long
Dim Res As String

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Specs")
iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = SH.Range("A2:A" & iLastRow)

Res = InputBox("Find which number?")

If Res = vbNullString Then
'Nothing to find,
Exit Sub
End If

Set RngFound = Rng.Find(What:=Res, _
After:=Rng.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not RngFound Is Nothing Then
'do something, e.g:
With RngFound
MsgBox .Address(0, 0)
.Interior.ColorIndex = 6
.Select
End With
Else
MsgBox Prompt:="The string " _
& Res & " was not found", _
Buttons:=vbCritical, _
Title:="Not Found!"
End If

End Sub
'<<=============

 

[Francis Hookham]

Thanks Norman - just what I wanted.

Francis

 

[Francis Hookham]

Hi Norman

I have got back to this and have adapted it to find the specified number and
it ends up selecting (and colouring) the cell with:

With RngFound
MsgBox .Address(0, 0)
.Interior.ColorIndex = 6
.Select
End With

I don't want to select the cell - I need the row number as a pointer to the
next part of the subroutine but I cannot find how to get the row number out
of this.

RowReqd = RtnFound.Row
No, that's no good!

Please...

Francis Hookham

 

[Dave Peterson]

Watch your spelling:

RowReqd = RtnFound.Row
may be:
RowReqd = RngFound.Row

(not sure if RowReqd is spelled correctly, though.)

 

[Francis Hookham]

Dave - I now see why it did not work - I had mistakenly defined

Dim RowReqd As Range

instead of

Dim RowReqd As Integer

All is well now - many thanks

Francis Hookham