search 2 Columns Please

G

Guest

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
 
T

Tom Ogilvy

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
 
G

Guest

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.
 
G

Guest

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
 
D

Dave Peterson

Delete the ">" sign
or just try:
Application.Goto Reference:=RgFound.Offset(0, -1)
 
T

Tom Ogilvy

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.
 
G

Guest

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
 
G

Guest

Hello Tom From Steved

I took out > it is now highlighting RgFound . Thankyou.

Loop While RgFound <> saddr
 
T

Tom Ogilvy

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
 
T

Tom Ogilvy

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
 
G

Guest

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 .
 
T

Tom Ogilvy

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
 
G

Guest

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.
 
G

Guest

Hello Tom from Steved

Thankyou the job used to take me 3 days to complete thanks to you it now takes

2 days.

Cheers.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MsgBox "Choose one ", vbYesNoCancel, " Three Options. " 3
Cancel if the first one is found 4
Why does this not stop when I push "yes' or "cancel in the message 14
Upgraded to Excel 2007 "Compile error" 2
I would like to create a button on the worksheet to work a macro 2
Was ok in 2003 not in 2007 1
Compile error 2
To find more than one Please 9

Top