loop with do while

[Olamide]

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

 

[Tom Ogilvy]

Sub AABBCC()
Dim l As String, r As String
Dim cell As Range, rng As Range
Dim bfirst As Boolean, bLast As Boolean
Dim cell1 As Range, cell2 As Range
For Each cell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
If Len(cell.Value) > 4 Then
If InStr(cell.Value, "/") Then
l = Left(cell.Value, 5)
r = Right(cell.Value, Len(cell.Value) - _
InStr(1, cell.Value, vbTextCompare) - 1)
If l = "89001" And CLng(r) >= 43 And Not bfirst _
And Not bLast Then
Set cell1 = cell
bfirst = True
End If
' check for /xx > 90
If l = "89001" And CLng(r) > 90 And bfirst Then
Set cell2 = cell.Offset(-1, 0)
bLast = True
Exit For
End If
' check for > 89001/
If l > "89001" And bfirst And Not bLast Then
Set cell2 = cell.Offset(-1, 0)
bLast = True
Exit For
End If
Else
If l > "89001" And bfirst And Not bLast Then
Set cell2 = cell.Offset(-1, 0)
bLast = True
Exit For
End If
End If
End If
Next cell
Range(cell1, cell2).Select
End Sub

 

[Joel]

the staement below won't work because you arre dealing with strings

from
If ActiveCell <> "89001/41" Then
to
if strcomp(ActiveCell,"89001/41") <> 0 then

 

[Tom Ogilvy]

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

 

[Olamide]

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]

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

 

[Olamide]

that is why i have "on error resume next" this will enter the if block and
test the condition "If ActiveCell <> "89001/41" Then "

 

[Tom Ogilvy]

Guess it must be working then. Wonder why you posted.