Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with do while
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with do while
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 -- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with do while
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 "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with do while
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with do while
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with do while
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with do while
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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop with do while
Guess it must be working then. Wonder why you posted.
-- Regards, Tom Ogilvy "Olamide" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |