PDA

View Full Version : Why does it find itself Loop



russkie
10-27-2005, 11:43 AM
hello.
Hey um, how come this ends up always finding itself and never stops the loop?
the offset is where the product's barcode is, there is no duplicate barcodes.


With Worksheets("Sheet1").Range("A:A")
Set InvCell = .Find(ProductInfo.Value, lookat:=xlWhole)
If Not InvCell Is Nothing Then
If (InvCell.Offset(0, 1).Value <> ProductInfo.Offset(0, 1).Value) Then
Newname = InputBox("aaThis product name already exists. Please Enter a name that is not being used." & vbLf & InvCell.Offset(0, 1).Value & vbLf & ProductInfo.Offset(0, 1).Value, "YF")
EDproductname.Value = Newname
ProductInfo.Value = Newname
GoTo CheckCopies
End If
If (InvCell.Offset(0, 1).Value = ProductInfo.Offset(0, 1).Value) Then
Firstaddress = InvCell.address

Do
Set InvCell = .FindNext(InvCell)
If Not InvCell Is Nothing Then
Newname = InputBox("ttThis product name already exists. Please Enter a name that is not being used." & vbLf & InvCell.Offset(0, 1).Value & vbLf & ProductInfo.Offset(0, 1).Value, "YF")
EDproductname.Value = Newname
ProductInfo.Value = Newname
GoTo CheckCopies
End If
Loop While Not InvCell Is Nothing And InvCell.address <> Firstaddress
End If
End If
End With

malik641
10-27-2005, 11:53 AM
I think it's the "IsNothing" piece in the "Loop While..." command. I'm not too familiar with the IsNothing function, but I've had similar problems with it.

Try this:


With Worksheets("Sheet1").Range("A:A")
Set InvCell = .Find(ProductInfo.Value, lookat:=xlWhole)
If Not InvCell Is Nothing Then
If (InvCell.Offset(0, 1).Value <> ProductInfo.Offset(0, 1).Value) Then
Newname = InputBox("aaThis product name already exists. Please Enter a name that is not being used." & _
vbLf & InvCell.Offset(0, 1).Value & vbLf & ProductInfo.Offset(0, 1).Value, "YF")
EDproductname.Value = Newname
ProductInfo.Value = Newname
GoTo CheckCopies
End If
If (InvCell.Offset(0, 1).Value = ProductInfo.Offset(0, 1).Value) Then
Firstaddress = InvCell.Address

Do
Set InvCell = .FindNext(InvCell)
If Not InvCell Is Nothing Then
Newname = InputBox("ttThis product name already exists. Please Enter a name that is not being used." & _
vbLf & InvCell.Offset(0, 1).Value & vbLf & ProductInfo.Offset(0, 1).Value, "YF")
EDproductname.Value = Newname
ProductInfo.Value = Newname
GoTo CheckCopies
End If
Loop While InvCell <> vbNullString And InvCell.Address <> Firstaddress
End If
End If
End With
Hope this helps :thumb

russkie
10-27-2005, 12:18 PM
nah, same thing.:think:

russkie
10-27-2005, 12:46 PM
Ok.. too much junk in my previuos code, heres simpler, but still same problem:


With Worksheets("Sheet1").Range("A:A")
Set InvCell = .Find(ProductInfo.Value, lookat:=xlWhole)
If Not InvCell Is Nothing Then
firstaddress = InvCell.address

Do
Set InvCell = .FindNext(InvCell)
If Not InvCell Is Nothing Then
Newname = InputBox("ttThis product name already exists. Please Enter_
a name that is not being used." & vbLf & InvCell.Offset(0, 1).Value & _
vbLf & ProductInfo.Offset(0, 1).Value, "YF")
EDproductname.Value = Newname
ProductInfo.Value = Newname
GoTo CheckCopies
End If
Loop While Not InvCell Is Nothing And InvCell.address <> firstaddress
End If
End With


Find productinfo.value it will find it. if it finds another copy, it shuold ask to change, if it doesnt, exit loop and continue with the rest of the code. Why after there are no copies, does it continue to just find itself and ask you to change??!!

vonpookie
10-27-2005, 12:57 PM
Say the value of ProductInfo is the word "test." You search for that word, and set the firstaddress variable.

From then on, the code is still performing the original search for the word "test," even when you change the value of ProductInfo. You have to initiate another .Find statement to search for a different value.

mvidas
10-27-2005, 01:18 PM
Hi Russkie
Your problem is in the line Loop While Not InvCell Is Nothing And InvCell.Address <> FirstaddressOnce it loops around and finds itself again, the addresses match but invcell is still not nothing.. you'd be better off using Loop Until InvCell is Nothing or InvCell.Address <> FirstaddressMatt

EDIT:
Actually, after thinking a little more, InvCell will never be nothing as it isn't changed, so even if there is one it will continually loop. You really only need: Loop Until InvCell.Address = FirstaddressMatt

russkie
10-27-2005, 01:43 PM
First i tried it, then i realized why that wouldnt work.
It will ask you to change the product name before it ever gets the the "loop" part of the code. Frankly, it never reaches there i think. So it doesnt matter how the Loop statement works. The problem is after it finds the first "productinfo.value" on the .findnext(invcell) if there is no other copy of the name then itll simply go back to the original, it doesnt even get to the "loop until" once on the .findnext it hits the original again, it asks to change the name... any ideas?

vonpookie
10-28-2005, 08:13 AM
A couple of questions:

- Where/What is ProductInfo? I'm assuming it is a range somewhere...

- You are searching column A for the value of ProductInfo. You want to change the name of any item having the same name (if more than 1 item is found). Should this not replace the one found in the list in column A? Currently you have it replacing the value of ProductInfo, which is what was being searched for in the first place.

Basically, I'm just a little confused on what you're trying to do, here...

russkie
10-28-2005, 09:05 AM
Yes, ProductInfo is a range. So i search for its value in Column A which is the name of the product. The simplified version is basically, find the first copy, whether its the one we are working with, or a copy of the name. Now if it finds a SECOND copy, then that must mean there are 2 items with the same name, and change "ProductInfo.Value" to something else, THEN, restart the searching process to look for the NEW "ProductInfo.Value" becuase now it is something else. ProductInfo is in column A, so when the user is prompted to change the name and does so, the restarted search will now look for the new value of ProductInfo and it does find the new one. But when there are NO copies, or when the copies are AFTER ProductInfo, it will only find ProductInfo twice... Its so strange because i ran something similiar in a different macro and everything was fine. By the way, it shouldnt change anything that this is in a UserForm right?

vonpookie
10-28-2005, 09:10 AM
if it finds a SECOND copy, then that must mean there are 2 items with the same name, and change "ProductInfo.Value" to something else, THEN, restart the searching process to look for the NEW "ProductInfo.Value" becuase now it is something else.

There's one catch, right there. The code is *not* looking for the *new* ProductInfo.value you entered as "Newname." As I posted yesterday:

Say the value of ProductInfo is the word "test." You search for that word, and set the firstaddress variable.

From then on, the code is still performing the original search for the word "test," even when you change the value of ProductInfo. You have to initiate another .Find statement to search for a different value.

russkie
10-28-2005, 09:17 AM
I dont understand though, If ProductInfo.Value = NewName, then the value of ProductInfo is now different, and when the program restarts, it will look for the new Value of ProductInfo, why would it search for the old one?
EDIT: infact, even if it DID search for the old one, it wouldnt find it! becuase in Column A where productinfo is, the old one no longer exists!

vonpookie
10-28-2005, 10:44 AM
This is a veeeery "rough draft" as it were, but is this something like what you're attempting to do?

Private Sub CommandButton1_Click()
Dim InvCell As Range, ProductInfo As Range
Dim NewName As String, firstaddress As String
Dim ProdCount As Integer, NewCount As Integer
Dim i As Integer, NamePrompt As String, chk As Boolean

NamePrompt = "This product name already exists. " _
& "Please Enter a name that is not in use."

With Sheets("Sheet1")
Set ProductInfo = .Range("C1")

'counts how many times the value is in column A
ProdCount = WorksheetFunction.CountIf(.Columns("A:A"), ProductInfo.Value)

With .Columns("A:A")
If ProdCount > 1 Then 'if more than 1 result found
Set InvCell = .Find(ProductInfo.Value, lookat:=xlWhole)
If Not InvCell Is Nothing Then
firstaddress = InvCell.Address
For i = 2 To ProdCount
Set InvCell = .FindNext(InvCell)
If Not InvCell Is Nothing Then
Do
chk = True
NewName = Application.InputBox( _
prompt:=NamePrompt & vbLf & _
InvCell.Offset(0, 1).Value & vbLf & _
ProductInfo.Offset(0, 1).Value, _
Title:="YF", Type:=2)
Select Case NewName
Case Is = False: Exit Sub 'cancel button clicked
Case Is = "", " "
MsgBox "Invalid Entry", vbExclamation
chk = False
Case Else
NewCount = WorksheetFunction.CountIf( _
.Columns("A:A"), NewName)
End Select
Loop While NewCount > 0 Or chk = False

InvCell = NewName
End If
Next i
End If
End If
End With
End With

End Sub