| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search 2 Columns Please
Hello from Steved
The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Sub FindPart()
Dim res, saddr as String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." exit Sub Else sAddr = RgFound.Address do if rgFound.Offset(0,1).Text = "3.30" then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) exit do set rgFound = RgToSearch.FindNext(rgFound) Loop while rgFound < sAddr End If if rgFound.Offst(0,1).Text < "3.30" then msgbox " School found, but not 3.30" End if End Sub -- Regards, Tom Ogilvy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Hello from Steved
It's first day back at work. All I need to dois hit the enter key and it will look for the next and so on. Thankyou. "Steved" wrote: Hello from Steved The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Hello Tom From Steved
Thankyou for your quick response. Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) The above is giving me the below error, What doI needto do please. Thanks --------------------------- Microsoft Visual Basic --------------------------- Compile error: Syntax error --------------------------- OK Help --------------------------- "Tom Ogilvy" wrote: Sub FindPart() Dim res, saddr as String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." exit Sub Else sAddr = RgFound.Address do if rgFound.Offset(0,1).Text = "3.30" then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) exit do set rgFound = RgToSearch.FindNext(rgFound) Loop while rgFound < sAddr End If if rgFound.Offst(0,1).Text < "3.30" then msgbox " School found, but not 3.30" End if End Sub -- Regards, Tom Ogilvy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Delete the "" sign
or just try: Application.Goto Reference:=RgFound.Offset(0, -1) Steved wrote: Hello Tom From Steved Thankyou for your quick response. Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) The above is giving me the below error, What doI needto do please. Thanks --------------------------- Microsoft Visual Basic --------------------------- Compile error: Syntax error --------------------------- OK Help --------------------------- "Tom Ogilvy" wrote: Sub FindPart() Dim res, saddr as String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." exit Sub Else sAddr = RgFound.Address do if rgFound.Offset(0,1).Text = "3.30" then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) exit do set rgFound = RgToSearch.FindNext(rgFound) Loop while rgFound < sAddr End If if rgFound.Offst(0,1).Text < "3.30" then msgbox " School found, but not 3.30" End if End Sub -- Regards, Tom Ogilvy -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
That is a result of editing it after I hit the reply button.
The "" were put in to show quoted text. I guess I didn't clean that on up. Just remove it. -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom From Steved Thankyou for your quick response. Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) The above is giving me the below error, What doI needto do please. Thanks --------------------------- Microsoft Visual Basic --------------------------- Compile error: Syntax error --------------------------- OK Help --------------------------- "Tom Ogilvy" wrote: Sub FindPart() Dim res, saddr as String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." exit Sub Else sAddr = RgFound.Address do if rgFound.Offset(0,1).Text = "3.30" then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) exit do set rgFound = RgToSearch.FindNext(rgFound) Loop while rgFound < sAddr End If if rgFound.Offst(0,1).Text < "3.30" then msgbox " School found, but not 3.30" End if End Sub -- Regards, Tom Ogilvy |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Hello from Steved
If I type in 001,8.00 The message box saing School 001, 8.00 not found but I can see it. 001 is in Col C and 8.00 is in Col D What is required please to correct the below macro Thankyou. Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = "3.30" Then Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do Set RgFound = RgToSearch.FindNext(RgFound) End If If RgFound.Offst(0, 1).Text < "3.30" Then MsgBox "School found, but not 3.30" End If Loop While RgFound < saddr End If End Sub "Steved" wrote: Hello from Steved It's first day back at work. All I need to dois hit the enter key and it will look for the next and so on. Thankyou. "Steved" wrote: Hello from Steved The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Hello Tom From Steved
I took out it is now highlighting RgFound . Thankyou. Loop While RgFound < saddr "Tom Ogilvy" wrote: That is a result of editing it after I hit the reply button. The "" were put in to show quoted text. I guess I didn't clean that on up. Just remove it. -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom From Steved Thankyou for your quick response. Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) The above is giving me the below error, What doI needto do please. Thanks --------------------------- Microsoft Visual Basic --------------------------- Compile error: Syntax error --------------------------- OK Help --------------------------- "Tom Ogilvy" wrote: Sub FindPart() Dim res, saddr as String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." exit Sub Else sAddr = RgFound.Address do if rgFound.Offset(0,1).Text = "3.30" then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) exit do set rgFound = RgToSearch.FindNext(rgFound) Loop while rgFound < sAddr End If if rgFound.Offst(0,1).Text < "3.30" then msgbox " School found, but not 3.30" End if End Sub -- Regards, Tom Ogilvy |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
There were a couple of typos in the code. Since you don't seem to be able
to debug the code, here is a tested version. Worked fine for me. The cell in column D should display 3.30 Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = "3.30" Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < "3.30" Then MsgBox " School found, but not 3.30" End If End Sub -- Regards, Tom Ogilvy |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
This is pretty much what I posted, but I made a variable to hold the 3.30 or
8.00 as a string. It found 8 formatted as 000 (stored as a number) and the string "008" Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") secondValue = "8.00" res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved If I type in 001,8.00 The message box saing School 001, 8.00 not found but I can see it. 001 is in Col C and 8.00 is in Col D What is required please to correct the below macro Thankyou. Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = "3.30" Then Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do Set RgFound = RgToSearch.FindNext(RgFound) End If If RgFound.Offst(0, 1).Text < "3.30" Then MsgBox "School found, but not 3.30" End If Loop While RgFound < saddr End If End Sub "Steved" wrote: Hello from Steved It's first day back at work. All I need to dois hit the enter key and it will look for the next and so on. Thankyou. "Steved" wrote: Hello from Steved The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Hello Tom
This is what I need I type in say 001,8.00 it will find 001,8.00. if I type in 041,7.50 or 034.3.25 I would like it to find those also. What I am explaining is that i've many entries, can your macro which you have kindly done for me be futher programmed, to be able to do multiple. Thankyou . "Tom Ogilvy" wrote: This is pretty much what I posted, but I made a variable to hold the 3.30 or 8.00 as a string. It found 8 formatted as 000 (stored as a number) and the string "008" Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") secondValue = "8.00" res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved If I type in 001,8.00 The message box saing School 001, 8.00 not found but I can see it. 001 is in Col C and 8.00 is in Col D What is required please to correct the below macro Thankyou. Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = "3.30" Then Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do Set RgFound = RgToSearch.FindNext(RgFound) End If If RgFound.Offst(0, 1).Text < "3.30" Then MsgBox "School found, but not 3.30" End If Loop While RgFound < saddr End If End Sub "Steved" wrote: Hello from Steved It's first day back at work. All I need to dois hit the enter key and it will look for the next and so on. Thankyou. "Steved" wrote: Hello from Steved The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Sub FindPart()
Dim res as String, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked If instr(1,res,",",vbtextcompare) = 0 then msgbox "Invalid entry" exit sub end if v = Application.Split(res,",") res = trim(v(lbound(v))) secondValue = trim(v(ubound(v))) Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom This is what I need I type in say 001,8.00 it will find 001,8.00. if I type in 041,7.50 or 034.3.25 I would like it to find those also. What I am explaining is that i've many entries, can your macro which you have kindly done for me be futher programmed, to be able to do multiple. Thankyou . "Tom Ogilvy" wrote: This is pretty much what I posted, but I made a variable to hold the 3.30 or 8.00 as a string. It found 8 formatted as 000 (stored as a number) and the string "008" Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") secondValue = "8.00" res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved If I type in 001,8.00 The message box saing School 001, 8.00 not found but I can see it. 001 is in Col C and 8.00 is in Col D What is required please to correct the below macro Thankyou. Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = "3.30" Then Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do Set RgFound = RgToSearch.FindNext(RgFound) End If If RgFound.Offst(0, 1).Text < "3.30" Then MsgBox "School found, but not 3.30" End If Loop While RgFound < saddr End If End Sub "Steved" wrote: Hello from Steved It's first day back at work. All I need to dois hit the enter key and it will look for the next and so on. Thankyou. "Steved" wrote: Hello from Steved The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Hello from Steved
If I type 041,7.50 I am getting the below error Object dosen't support this property or method. Please what is Required to the below macro to correct this. Thankyou. "Tom Ogilvy" wrote: Sub FindPart() Dim res as String, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked If instr(1,res,",",vbtextcompare) = 0 then msgbox "Invalid entry" exit sub end if v = Application.Split(res,",") res = trim(v(lbound(v))) secondValue = trim(v(ubound(v))) Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom This is what I need I type in say 001,8.00 it will find 001,8.00. if I type in 041,7.50 or 034.3.25 I would like it to find those also. What I am explaining is that i've many entries, can your macro which you have kindly done for me be futher programmed, to be able to do multiple. Thankyou . "Tom Ogilvy" wrote: This is pretty much what I posted, but I made a variable to hold the 3.30 or 8.00 as a string. It found 8 formatted as 000 (stored as a number) and the string "008" Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") secondValue = "8.00" res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved If I type in 001,8.00 The message box saing School 001, 8.00 not found but I can see it. 001 is in Col C and 8.00 is in Col D What is required please to correct the below macro Thankyou. Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = "3.30" Then Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do Set RgFound = RgToSearch.FindNext(RgFound) End If If RgFound.Offst(0, 1).Text < "3.30" Then MsgBox "School found, but not 3.30" End If Loop While RgFound < saddr End If End Sub "Steved" wrote: Hello from Steved It's first day back at work. All I need to dois hit the enter key and it will look for the next and so on. Thankyou. "Steved" wrote: Hello from Steved The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Change
v = Application.Split(res,",") to v = Split(res,",") -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved If I type 041,7.50 I am getting the below error Object dosen't support this property or method. Please what is Required to the below macro to correct this. Thankyou. "Tom Ogilvy" wrote: Sub FindPart() Dim res as String, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked If instr(1,res,",",vbtextcompare) = 0 then msgbox "Invalid entry" exit sub end if v = Application.Split(res,",") res = trim(v(lbound(v))) secondValue = trim(v(ubound(v))) Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom This is what I need I type in say 001,8.00 it will find 001,8.00. if I type in 041,7.50 or 034.3.25 I would like it to find those also. What I am explaining is that i've many entries, can your macro which you have kindly done for me be futher programmed, to be able to do multiple. Thankyou . "Tom Ogilvy" wrote: This is pretty much what I posted, but I made a variable to hold the 3.30 or 8.00 as a string. It found 8 formatted as 000 (stored as a number) and the string "008" Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") secondValue = "8.00" res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved If I type in 001,8.00 The message box saing School 001, 8.00 not found but I can see it. 001 is in Col C and 8.00 is in Col D What is required please to correct the below macro Thankyou. Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = "3.30" Then Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do Set RgFound = RgToSearch.FindNext(RgFound) End If If RgFound.Offst(0, 1).Text < "3.30" Then MsgBox "School found, but not 3.30" End If Loop While RgFound < saddr End If End Sub "Steved" wrote: Hello from Steved It's first day back at work. All I need to dois hit the enter key and it will look for the next and so on. Thankyou. "Steved" wrote: Hello from Steved The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
search 2 Columns Please
Hello Tom from Steved
Thankyou the job used to take me 3 days to complete thanks to you it now takes 2 days. Cheers. "Tom Ogilvy" wrote: Change v = Application.Split(res,",") to v = Split(res,",") -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved If I type 041,7.50 I am getting the below error Object dosen't support this property or method. Please what is Required to the below macro to correct this. Thankyou. "Tom Ogilvy" wrote: Sub FindPart() Dim res as String, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked If instr(1,res,",",vbtextcompare) = 0 then msgbox "Invalid entry" exit sub end if v = Application.Split(res,",") res = trim(v(lbound(v))) secondValue = trim(v(ubound(v))) Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom This is what I need I type in say 001,8.00 it will find 001,8.00. if I type in 041,7.50 or 034.3.25 I would like it to find those also. What I am explaining is that i've many entries, can your macro which you have kindly done for me be futher programmed, to be able to do multiple. Thankyou . "Tom Ogilvy" wrote: This is pretty much what I posted, but I made a variable to hold the 3.30 or 8.00 as a string. It found 8 formatted as 000 (stored as a number) and the string "008" Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Dim secondValue As String Set RgToSearch = ActiveSheet.Range("C:C") secondValue = "8.00" res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = secondValue Then Application.Goto Reference:= _ RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do End If Set RgFound = RgToSearch.FindNext(RgFound) Loop While RgFound.Address < saddr End If If RgFound.Offset(0, 1).Text < secondValue Then MsgBox " School found, but not 8.00" End If End Sub -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved If I type in 001,8.00 The message box saing School 001, 8.00 not found but I can see it. 001 is in Col C and 8.00 is in Col D What is required please to correct the below macro Thankyou. Sub FindPart() Dim res, saddr As String Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", _ "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Exit Sub Else saddr = RgFound.Address Do If RgFound.Offset(0, 1).Text = "3.30" Then Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) Exit Do Set RgFound = RgToSearch.FindNext(RgFound) End If If RgFound.Offst(0, 1).Text < "3.30" Then MsgBox "School found, but not 3.30" End If Loop While RgFound < saddr End If End Sub "Steved" wrote: Hello from Steved It's first day back at work. All I need to dois hit the enter key and it will look for the next and so on. Thankyou. "Steved" wrote: Hello from Steved The below will find what I require in Column C ie 002 What do I need to do please for it to search Col C for 002 and Col D for 3.30 Both are on the same row. Thankyou. Sub FindPart() Dim res Dim RgToSearch As Range, RgFound As Range Set RgToSearch = ActiveSheet.Range("C:C") res = Application.InputBox("Type School Number", "Find School", , , , , , 2) If res = "False" Then Exit Sub 'exit if Cancel is clicked res = Trim(UCase(res)) If res = "" Then Exit Sub 'exit if no entry and OK is clicked Set RgFound = RgToSearch.Find(what:=res, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If RgFound Is Nothing Then MsgBox "School " & res & " not found." Else Application.Goto Reference:=RgFound.Offset(0, -1).Address(True, True, xlR1C1) End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search for matches in two columns | Excel Worksheet Functions | |||
| search multiple columns | Excel Discussion (Misc queries) | |||
| Search Across Multiple Columns | Excel Discussion (Misc queries) | |||
| search columns, set variable value | Excel Programming | |||
| using autofilter to search 2 columns | Excel Programming | |||
Linear Mode
