I no see you logic, but for the other reasons I gave, that will not work. If
it isn't found, the select on the end of the FIND statement will raise an
error and your code will crap out.

without fixing any of the other problems

Dim rng as Range

set rng = Columns(1).Find(What:="89001/41", After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If rng is nothing then
' value not found
Range("A2").Select


--
Regards,
Tom Ogilvy



"Olamide" wrote:

i am back to A2 becos to enter the if statement, that means i cannot find
89001/41 therefore no cell will be selected

"Tom Ogilvy" wrote:

change 89001/41 in your worksheet (not your code) to 89001/42 so that there
is no 89001/41 and your code will error.

Dim OtherBank As Variant
Const OBStart As Variant = "89000/41"
Const OBEnd As Variant = "89999/43"
Columns("A:A").Select ' Find Other Bank and Cash
On Error Resume Next
Selection.Find(What:="89001/41", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
' if you haven't errored at this point, your active cell will equal
"89001/41"
' so the code contained in your if statement will be skipped over
If ActiveCell < "89001/41" Then
OtherBank = Val(Selection.Value)
' Otherbank will now hold "89001" or equivalent as a number
Range("A2").Select
' Now you are back at cell A2 - not sure why you want to start
' from the top again. You have thrown away any record of
' the cell you found containing 89001/41, so not sure why
' you searched for it.

' you comparison is like 89001 < "89001/41"
' this gives me a type mismatch error
' you probably want to "Dim Otherbank as String" at the top
Do While OtherBank < OBStart Or OtherBank OBEnd
Selection.Offset(1, 0).Select
OtherBank = Val(Selection.Value)
Loop
End If

Selection.EntireRow.Insert

--
regards,
Tom Ogilvy


"Olamide" wrote:

below refuse to work. My objective is to search for 89001/43 in column A. if
my search enters run time error the if block will be executed. Inside the Do
block i want the 1st cell in column A that contain any value within the range
specified in the do block to be selected

Dim OtherBank As Variant
Const OBStart As Variant = "89000/41"
Const OBEnd As Variant = "89999/43"
Columns("A:A").Select ' Find Other Bank and Cash
On Error Resume Next
Selection.Find(What:="89001/41", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
If ActiveCell < "89001/41" Then
OtherBank = Val(Selection.Value)
Range("A2").Select
Do While OtherBank < OBStart Or OtherBank OBEnd
Selection.Offset(1, 0).Select
OtherBank = Val(Selection.Value)
Loop
End If

Selection.EntireRow.Insert