Find in a column
 

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" wrote in message
...
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
'<<============
---
Regards,
Norman



"Francis Hookham" wrote in message
...
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

Find in a column
 

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
'<<=============


---
Regards,
Norman

Find in a column
 

Thanks Norman - just what I wanted.

Francis


"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman

Find in a column
 

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


"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman

Find in a column
 

Watch your spelling:

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

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

Francis Hookham wrote:

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

"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman


--

Dave Peterson

Find in a column
 

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

"Dave Peterson" wrote in message
...
Watch your spelling:

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

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

Francis Hookham wrote:

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

"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman


--

Dave Peterson