Hello Dave from Steved

This is what I needed and I thankyou.

Dave the reason I do not use filters is that this file is on a network and
is live 24/7, so that others did'nt get confused, hence the reason for your
code.

Once again I thankyou for your patience

Have a great day.

"Dave Peterson" wrote:

If both are numbers, then the formula that you'd use in a worksheet cell would
look like:

=sumproduct(--(c1:c99=1),--(d1:d99)=3.25))

The double quotes aren't necessary and would make the results incorrect.

So try changing this line:

HowManyMatches = wks.Evaluate("sumproduct(--(" & RngToSearch.Address _
& "=" & Chr(34) & FirstValue & Chr(34) & ")," _
& "--(" & RngToSearch.Offset(0, 1).Address _
& "=" & SecondValue & "))")

to

HowManyMatches = wks.Evaluate("sumproduct(--(" & RngToSearch.Address _
& "=" & FirstValue & ")," _
& "--(" & RngToSearch.Offset(0, 1).Address _
& "=" & SecondValue & "))")




Steved wrote:

Hello from Steved

to look for 001,3.25

"001" is a number and "3.25" is a decimal time.

The below finds everything in C:C as +0 will treat it as a number.

=SUMPRODUCT(--(C1:C1000="001"+0))

The below finds everything in D:D as +0 will treat it as a number.

=SUMPRODUCT(--(D1:D1000="3.25"+0))

ok what are my objectives

1 find 001 in column C:C

2 if 001 found in Column C:C then find 3.25 in D:D

Result is in Row 17 we have 001,3.25

3 move to the Column B:B in this case "B17"

4 in Column B:B I have Data That's need to be updated "B17"

Is it possible please to have a message box do the above.

I know gentlemen I'm a challenge but hopefully I've made this exercise more
clearer.

ps by putting in "+0" it finds every instance

I Thankyou.

"Dave Peterson" wrote:

First, it's difficult to guess what you really want just by looking at your
code. But my guess is that you want to find the first row that has a match in
column C and a match in column D for the numbers you input into that inputbox.

Then if one is found, you want to be prompted to look for the next or just stay
where you are.

==================================
An aside...

Personally, I wouldn't use a macro for this. I'd just apply
data|filter|autofilter to the range and filter column C to show all the values
that I want to see and then filter column D to see the same thing.

I could see all the entries at once and not have to be bothered with the macro
-- that always starts at the top!
==================================

But if you want....

Here are my assumptions.

The stuff in column C is text--not numbers formatted to show leading 0's.
'001 not 1 with a format of "001"

But the stuff in column D is really a number but it's formatted to show 2
decimals.

But these are just guesses based on the use of .Text in your code.

If that's true, then I'm going to count the number of rows that match the stuff
entered into that inputbox using a worksheet function.

I could use something like this in a cell:
=sumproduct(--(c1:c99="001"),--(d1:d99)=8))

If my assumptions are wrong, then the quotes will be screwed up. And the code
will have to change.

But to correct the code, you'll have to share what the data really is -- and
you'll have to make sure that all the data is what you say it is. No 99%
accuracy. It has to be 100% consistent.

Anyway...

If you want to try:

Option Explicit
Sub Schoolfind()

Dim res As String
Dim myOpt As Long

Dim RngToSearch As Range
Dim RngFound As Range

Dim FirstValue As String
Dim SecondValue As String

Dim v As Variant
Dim wks As Worksheet
Dim HowManyMatches As Long 'total records with matches
Dim fCtr As Long 'counter of these found matches
Dim iCtr As Long 'just a counter for anything
Dim KeepLooking As Boolean

Set wks = ActiveSheet

With wks
Set RngToSearch = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With

'no need for application.inputbox and type:=2
res = InputBox(Prompt:="Type School Number as 001,8.00 to find the " _
& "school you are looking for", Title:="Find School", _
Default:="001,8.00")

'remove all spaces!
res = Replace(res, " ", "")
If res = "" Then
Exit Sub 'exit if no entry and OK is clicked
End If

v = Split(res, ",")
If UBound(v) - LBound(v) + 1 < 2 Then
MsgBox "Please enter exactly two criterial!"
Exit Sub
End If

For iCtr = LBound(v) To UBound(v)
If IsNumeric(v(iCtr)) = False Then
MsgBox "Both criteria must be numeric!"
Exit Sub
End If
Next iCtr

FirstValue = v(LBound(v))
SecondValue = v(UBound(v))

'one way to find out how many matches are in the worksheet is
'to use a formula like:
'=sumproduct(--(c1:c99="001"),--(d1:d99)=8))
'it would look like this in code:
HowManyMatches = wks.Evaluate("sumproduct(--(" & RngToSearch.Address _
& "=" & Chr(34) & FirstValue & Chr(34) & "),"
_
& "--(" & RngToSearch.Offset(0, 1).Address _
& "=" & SecondValue & "))")

'just for testing!
MsgBox HowManyMatches & " rows with matches!"

If HowManyMatches = 0 Then
MsgBox "Nothing found with both columns matching!"
Exit Sub
End If

With RngToSearch
Set RngFound = .Cells.Find(What:=FirstValue, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
Searchorder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

KeepLooking = True
fCtr = 0
Do
If RngFound.Offset(0, 1).Value < CDbl(SecondValue) Then
'do nothing special--keep looking
Else
'found a match
fCtr = fCtr + 1
Application.Goto Reference:=RngFound.Offset(0, -1)
If fCtr = HowManyMatches Then
MsgBox Prompt:="This is the last one!", _
Title:="On: " & fCtr & " of " & HowManyMatches
KeepLooking = False
Exit Do
Else
myOpt = MsgBox(Prompt:="Keep looking?", _
Buttons:=vbYesNo, _
Title:="On: " & fCtr & " of " & HowManyMatches)
If myOpt = vbNo Then
'stop looking
KeepLooking = False
Exit Do
End If
End If
End If

If KeepLooking = True Then
Set RngFound = RngToSearch.FindNext(RngFound)
End If
Loop

End Sub







Steved wrote:

Hello from Steved

Please why does this not stop when I push "yes' or "cancel in the message box.

I can have up to 4 of the same occurences, for example I might what it to
carry on after finding the first instance and stop it on the 3rd.

I thankyou.

Sub Schoolfind()
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 as 001,8.00 to find the
school you are looking for", _
"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 = 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)
' commenting out the next line should do it
MsgBox "Choose One", vbYesNoCancel, "Three Options."
' 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 Not Found"
End If
End Sub

--

Dave Peterson


--

Dave Peterson