ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   search 2 Columns Please (https://www.excelbanter.com/excel-programming/353104-search-2-columns-please.html)

Steved
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

Tom Ogilvy
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

Steved
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

Steved
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

Dave Peterson
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

Tom Ogilvy
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

Steved
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

Steved
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

Tom Ogilvy
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

Tom Ogilvy
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

Steved
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

Tom Ogilvy
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

Steved
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

Tom Ogilvy
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

Steved
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


All times are GMT +1. The time now is 10:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com