search 2 Columns Please

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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.
 
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
 
Delete the ">" sign
or just try:
Application.Goto Reference:=RgFound.Offset(0, -1)
 
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.
 
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
 
Hello Tom From Steved

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

Loop While RgFound <> saddr
 
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
 
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
 
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 .
 
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
 
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.
 
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

Back
Top