Solved: .FindNext situation....

[russkie]

hello,
i have list i search for a barcode. Now when i have the barcode i can manipulate the information on that row. What i didnt expect, and now drives me mad, is that 2 different books by the same company have the same barcodes... schmucks...

So what i need is for my macro, when the user is scanning out a book, to check our list to make sure there isnt 2 copies of that barcode, if there is, present the user with more information on each barcode and ask him to pick which one he is sending out. Cool. so i got this:


With WS1.Range("B:B")
Set ItemCell = .Find(scanned, lookat:=xlWhole)
If Not ItemCell Is Nothing Then
Set RmmbrItem = ItemCell
Set ItemCell = .FindNext(ItemCell)
If Not ItemCell Is Nothing Then
pickone = InputBox("We have found 2 items with the same barcode: " & _
vbLf & vbLf & RmmbrItem.Offset(0, -1).Value & vbLf & _
ItemCell.Offset(0, -1).Value & vbLf & vbLf & _
"Please type in the Name of the book you wish to send.", "Oy!")
If (pickone = RmmbrItem.Offset(0, -1).Value) Then
Set ItemCell = RmmbrItem
GoTo CheckPrice
End If
If (pickone <> ItemCell.Offset(0, -1).Value) Then
MsgBox "You did not enter either of those, Restarting..."
GoTo GetBookInfo
End If
End If
GoTo CheckPrice
...
...


Ok, runs cool if 2 conditions are met:
1) there is a copy of that barcode
2)there is only one more copy of that barcode..

pretty junky conditions.
Sooo, when there is no copy of the barcode, itll present the user with 2 of the same item, same info and barcode, basically itll just loop the .FindNext and find the same item it did the first time, not cool, how do i get it not to do that?
Also, how can i get it to remember each item without "Dim"-ing a ton of preset Vars and looping it a billion times to check if there is more than one copy, meaning, i want it to (Dim "RmmbrItem" + 1 as range) on each loop, how do i do that? Or there is a better way?

Thanks alot for any help.

[russkie]

well, actually, i figured out the first part, sorta...
i just did:



If Not ItemCell Is Nothing Then
If (ItemCell.Offset(0, -1).Value = RmmbrItem.Offset(0, -1).Value) Then GoTo CheckPrice




So, if there is only one copy, the item id which is in the offset will be the same and itll skip whatevers after.

Is there a better/smarter way of doing this?
And also if anyone has something for the second Q

[mdmackillop]

Hi Russkie,
If you add line breaks to your code, it keeps it on the screen to be seen without scrolling
Regards
MD

[mdmackillop]

Hi Russkie,
This is copied from the Help file. Use the FirstAddress method to exit the loop when it returns to the original cell.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

[mdmackillop]

I've fiddled with your code to add an array to store multiple finds and show them in the message box. I think though, that you might run into practical problems, as any misspelling of the book name will result in "Not Found". You would be better running this code from a UserForm where the multiple listing could be added to a list or combobox, and the appropriate book selected. If you need any help with this, let us know.
Regards
MD

[russkie]

yea i figured itd be cool with an array i just wasnt sure howta work em in VBA.

Thanks alot MD, ill play around with putting the list in a userform, ur prob right with that situation.

[mdmackillop]

I had a little time to play around with this. See http://www.vbaexpress.com/forum/showthread.php?t=5741

[russkie]

Lordy... thats so perfect, wanna smooch MD?

Hey, can u check out my post on the var/range from the from, directly from here i jsut thought id open new post cuz it was teaching different topic...
:bow:

[mdmackillop]

Happy to help Russkie.
I hadn't seen your other post so I might move my answer there to be relevant.
MD