| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to check cells for numbers between 2 number range
i have a code like this
Columns("A:A").Select ' Find Other Bank and Cash On Error Resume Next Selection.Find(What:="89001/43", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If ActiveCell < 89001/43 Then Range("A1").Select Do While Selection < 89000 Or Selection 89176 Selection.Offset(1, 0).Select Loop End If my problem is that the slash is alway converted to division. Can somebody pls help me. thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop to check cells for numbers between 2 number range
If ActiveCell < "89001/43"
-- regards, Tom Ogilvy "Olamide" wrote: i have a code like this Columns("A:A").Select ' Find Other Bank and Cash On Error Resume Next Selection.Find(What:="89001/43", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If ActiveCell < 89001/43 Then Range("A1").Select Do While Selection < 89000 Or Selection 89176 Selection.Offset(1, 0).Select Loop End If my problem is that the slash is alway converted to division. Can somebody pls help me. thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop to check cells for numbers between 2 number range
Tom the "Do While Selection < 89000 Or Selection 89176" will not work if
selection 89001/43 "Tom Ogilvy" wrote: If ActiveCell < "89001/43" -- regards, Tom Ogilvy "Olamide" wrote: i have a code like this Columns("A:A").Select ' Find Other Bank and Cash On Error Resume Next Selection.Find(What:="89001/43", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If ActiveCell < 89001/43 Then Range("A1").Select Do While Selection < 89000 Or Selection 89176 Selection.Offset(1, 0).Select Loop End If my problem is that the slash is alway converted to division. Can somebody pls help me. thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop to check cells for numbers between 2 number range
What are you trying to do?
Sub findtext1()' finds this text Columns("a").Find("89001/43").Select End Sub -- Don Guillett SalesAid Software "Olamide" wrote in message ... i have a code like this Columns("A:A").Select ' Find Other Bank and Cash On Error Resume Next Selection.Find(What:="89001/43", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If ActiveCell < 89001/43 Then Range("A1").Select Do While Selection < 89000 Or Selection 89176 Selection.Offset(1, 0).Select Loop End If my problem is that the slash is alway converted to division. Can somebody pls help me. thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop to check cells for numbers between 2 number range
what of if i want to find range of 89001/43 to 89001/90 and select cell that
contain the first number within the range. can the quote still work in this. "Olamide" wrote: i have a code like this Columns("A:A").Select ' Find Other Bank and Cash On Error Resume Next Selection.Find(What:="89001/43", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If ActiveCell < 89001/43 Then Range("A1").Select Do While Selection < 89000 Or Selection 89176 Selection.Offset(1, 0).Select Loop End If my problem is that the slash is alway converted to division. Can somebody pls help me. thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop to check cells for numbers between 2 number range
What if 89001/43 isn't there. I thought that was you problem
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 Next cell should select the range of 89001/43 to 89001/90 inclusive assuming the data is sorted to support this. -- Regards, Tom Ogilvy "Olamide" wrote: what of if i want to find range of 89001/43 to 89001/90 and select cell that contain the first number within the range. can the quote still work in this. "Olamide" wrote: i have a code like this Columns("A:A").Select ' Find Other Bank and Cash On Error Resume Next Selection.Find(What:="89001/43", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If ActiveCell < 89001/43 Then Range("A1").Select Do While Selection < 89000 Or Selection 89176 Selection.Offset(1, 0).Select Loop End If my problem is that the slash is alway converted to division. Can somebody pls help me. thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop to check cells for numbers between 2 number range
Tom,
this is complex. i cannot even interpret it. Sipe Olamide Lagos, Nigeria "Tom Ogilvy" wrote: What if 89001/43 isn't there. I thought that was you problem 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 Next cell should select the range of 89001/43 to 89001/90 inclusive assuming the data is sorted to support this. -- Regards, Tom Ogilvy "Olamide" wrote: what of if i want to find range of 89001/43 to 89001/90 and select cell that contain the first number within the range. can the quote still work in this. "Olamide" wrote: i have a code like this Columns("A:A").Select ' Find Other Bank and Cash On Error Resume Next Selection.Find(What:="89001/43", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If ActiveCell < 89001/43 Then Range("A1").Select Do While Selection < 89000 Or Selection 89176 Selection.Offset(1, 0).Select Loop End If my problem is that the slash is alway converted to division. Can somebody pls help me. thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop to check cells for numbers between 2 number range
there is a typo in it anyway.
r = Right(cell.Value, Len(cell.Value) - _ InStr(1, cell.Value, vbTextCompare)) should be r = Right(cell.Value, Len(cell.Value) - _ InStr(1, cell.Value, "/", vbTextCompare)) but now you should see why I don't spend a lot of time writing code for you - it would never get used. -- Regards, Tom Ogilvy "Olamide" wrote: Tom, this is complex. i cannot even interpret it. Sipe Olamide Lagos, Nigeria "Tom Ogilvy" wrote: What if 89001/43 isn't there. I thought that was you problem 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 Next cell should select the range of 89001/43 to 89001/90 inclusive assuming the data is sorted to support this. -- Regards, Tom Ogilvy "Olamide" wrote: what of if i want to find range of 89001/43 to 89001/90 and select cell that contain the first number within the range. can the quote still work in this. "Olamide" wrote: i have a code like this Columns("A:A").Select ' Find Other Bank and Cash On Error Resume Next Selection.Find(What:="89001/43", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select If ActiveCell < 89001/43 Then Range("A1").Select Do While Selection < 89000 Or Selection 89176 Selection.Offset(1, 0).Select Loop End If my problem is that the slash is alway converted to division. Can somebody pls help me. thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Check number within a range | Excel Worksheet Functions | |||
| loop through cells in a range and pick up corresponding cell values in another range | Excel Programming | |||
| How do I check repeating sets of number in a range | Excel Worksheet Functions | |||
| Check if a number exists in a range? | Excel Discussion (Misc queries) | |||
| Create a Loop to check cells between workbooks | Excel Programming | |||
Linear Mode
