Entering a range of numbers and searching same

[Dave O]

I'm wrong again. The code works even with gaps at the top (I was
entering an invalid doc number). The code works properly, so there
must be something else going on. Does it work properly sometimes, and
not others?

 

[Guest]

That would make life too easy for me (and you!) It doesn't start in cell A1.
The beginning range of numbers starts in D2. Ending Range in E2. (I have a
header row). And R1 is where I have the field that I type in the document
number I'm searching for. This is what I have as of now:

Sub FindDoc()
Dim r As Range
Dim j, k, l As Long
Set r = Intersect(ActiveSheet.UsedRange, Columns("A:A"))
j = r.Rows.Count
k = --InputBox("Enter document number:")
Range("r1").Value = k
For l = 1 To j
If k >= Cells(l, 1).Value Then
If k <= Cells(l, 2).Value Then
Cells(l, 3).Select
MsgBox ("Document Number " & k & " Client " & Cells(l, 3).Value)
Exit Sub
End If
End If
Next
MsgBox ("Document Number " & k & " not assigned")
End Sub

Thanks for being so patient with me. I'm learning but it's not quick enough
for me!

Penny

 

[Guest]

Hello again! I just realized something. It doesn't give me the "doc number
not assigned" if I'm looking up a number that is in the beginning or in the
ending range. But it does give me the message if it's a number within the
range. (Hope I'm making sense?) If it's a number within the range, it does
format it as specified. With either outcome, it doesn't automatically go to
that row.

Thanks again for all of your time.

Penny

 

[Dave O]

Try this instead. We should be getting close, now!

Sub FindDoc()
Dim r As Range
Dim j, k, l As Long
Set r = Intersect(ActiveSheet.UsedRange, Columns("d:d"))
j = r.Rows.Count
k = --InputBox("Enter document number:")
Range("r1").Value = k
For l = 1 To j
If k >= Cells(l, 4).Value Then
If k <= Cells(l, 5).Value Then
Cells(l, 6).Select
MsgBox ("Document Number " & k & " Client " & Cells(l, 6).Value)
Exit Sub
End If
End If
Next
MsgBox ("Document Number " & k & " not assigned")
End Sub

 

[Guest]

Absolutely Amazing!!!! It works and works wonderfully!!!! Thank you so much
for all of your help. Can't say it enough. Wish I could return the favor.
I am so grateful.

 

[Dave O]

Have to thank Chuck for that in a big way. Glad it worked out for you!

 

[Guest]

I meant to send that as a thanks to all of you. I really do appreciate it.
Hope you have a great and relaxing afternoon!

Penny

 

[CLR]

Thanks for the thanks, Penny and Dave O. I've been home sick today, sorry I
missed out on the fun. Glad it's all working for you Penny........and, you
done good Dave O.

Vaya con Dios,
Chuck, CABGx3

 

[Dave O]

This is starting to feel like a group hug. Yuk!

Kidding, of course. Chuck, hope you're feeling better. Penny, hope
you had a good experience with the Usenet- let us know if we can help
with anything else.