Product not found error

P

Pat

Could someone help explain why I am getting a "product not found" error in
the following code:

Dim sProd As String
Dim icol As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range, Target As Range
Dim res As Variant
Dim rng1F As Range
Dim rng1C As Range

Set rng = Worksheets("ControlCentre").Range("AR30")
res = Application.Match(ActiveSheet. _
Range("R25").Value, rng, 0)
If Not IsError(res) Then
icol = rng(res).Column
Else
MsgBox "Invoice not matched"
Exit Sub
End If
With ActiveSheet
With ActiveSheet
Set rng1 = Nothing
Set rng1C = Nothing
Set rng1F = Nothing
On Error Resume Next
Set rng1C = .Range("R74:R1000"). _
SpecialCells(xlConstants, xlNumbers)
Set rng1F = .Range("R74:R1000"). _
SpecialCells(xlFormulas, xlNumbers)
On Error GoTo 0
If rng1C Is Nothing Then
Set rng1 = rng1F
Else
If rng1F Is Nothing Then
Set rng1 = rng1C
Else
Set rng1 = Union(rng1F, rng1C)
End If
End If
If rng1 Is Nothing Then
MsgBox "No Quantities in Invoice"
Exit Sub
End If
End With

For Each Target In rng1
sProd = Target.Parent.Cells(Target.Row, 24)

res = Application.Match(sProd, _
Worksheets("ControlCentre").Range("C77:C1000"), 0)
If Not IsError(res) Then
With Worksheets("ControlCentre")
If IsNumeric(Target.Value) Then
Set rng2 = .Cells(res + 76, icol)
rng2.Value = rng2.Value + Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd


End With
End If
Else
MsgBox Target.Address & " Caused an Error"
Resume Next
End If
End If
Next

Also the End With and End If statement perhaps correct, could you verifiy
this also?

Very much appreciate any help given.
Pat
 
T

Tom Ogilvy

The message would indicate that sProd is not in the range being searched.

I have cleaned up your structures as I understand them.

Sub AA()
Dim sProd As String
Dim icol As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range, Target As Range
Dim res As Variant
Dim rng1F As Range
Dim rng1C As Range

Set rng = Worksheets("ControlCentre").Range("AR30")
res = Application.Match(ActiveSheet. _
Range("R25").Value, rng, 0)
If Not IsError(res) Then
icol = rng(res).Column
Else
MsgBox "Invoice not matched"
Exit Sub
End If

With ActiveSheet
Set rng1 = Nothing
Set rng1C = Nothing
Set rng1F = Nothing
On Error Resume Next
Set rng1C = .Range("R74:R1000"). _
SpecialCells(xlConstants, xlNumbers)
Set rng1F = .Range("R74:R1000"). _
SpecialCells(xlFormulas, xlNumbers)
On Error GoTo 0
If rng1C Is Nothing Then
Set rng1 = rng1F
Else
If rng1F Is Nothing Then
Set rng1 = rng1C
Else
Set rng1 = Union(rng1F, rng1C)
End If
End If
If rng1 Is Nothing Then
MsgBox "No Quantities in Invoice"
Exit Sub
End If
End With

For Each Target In rng1
sProd = Target.Parent.Cells(Target.Row, 24)
res = Application.Match(sProd, _
Worksheets("ControlCentre").Range("C77:C1000"), 0)
If Not IsError(res) Then
With Worksheets("ControlCentre")
If IsNumeric(Target.Value) Then
Set rng2 = .Cells(res + 76, icol)
rng2.Value = rng2.Value + Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd
End If
Next

End Sub
 
P

Pat

I am puzzled to what is happening and to why sProd is not picking up the
range. Its probably stairing me in the face.

If I could go through the code perhaps it can be seen what the problem is.
The code is executed from the Invoice! sheet:
Sub AA()
Dim sProd As String
Dim icol As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range, Target As Range
Dim res As Variant
Dim rng1F As Range
Dim rng1C As Range
Click to expand...


ControlCentre!AR30 contains the name "Plist Totals"
Set rng = Worksheets("ControlCentre").Range("AR30")
Click to expand...


R25 is in the Invoice! sheet and contains the name "Plist Totals"
res = Application.Match(ActiveSheet. _
Range("R25").Value, rng, 0)
If Not IsError(res) Then
icol = rng(res).Column
Else
MsgBox "Invoice not matched"
Exit Sub
End If

With ActiveSheet
Set rng1 = Nothing
Set rng1C = Nothing
Set rng1F = Nothing
On Error Resume Next
Click to expand...


R74:R1000 is in the Invoice! sheet and contain the results of a formula in
each of the cells.
Set rng1C = .Range("R74:R1000"). _
SpecialCells(xlConstants, xlNumbers)
Set rng1F = .Range("R74:R1000"). _
SpecialCells(xlFormulas, xlNumbers)
SpecialCells(xlFormulas, xlNumbers)
On Error GoTo 0
If rng1C Is Nothing Then
Set rng1 = rng1F
Else
If rng1F Is Nothing Then
Set rng1 = rng1C
Else
Set rng1 = Union(rng1F, rng1C)
End If
End If
If rng1 Is Nothing Then
MsgBox "No Quantities in Invoice"
Exit Sub
End If
End With
For Each Target In rng1
Click to expand...


Target.Row 24 is column X in the Invoice! sheet and contain the names of
products
sProd = Target.Parent.Cells(Target.Row, 24)
Click to expand...


C77:C1000 is in ControlCentre and like above contains the names of products.
res = Application.Match(sProd, _
Worksheets("ControlCentre").Range("C77:C1000"), 0)
If Not IsError(res) Then
With Worksheets("ControlCentre")
If IsNumeric(Target.Value) Then
Click to expand...


res + 76 is column ControlCentre!AR
Set rng2 = .Cells(res + 76, icol)
rng2.Value = rng2.Value + Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd
End If
Next

End Sub
Click to expand...


Regards
Pat
 
T

Tom Ogilvy

this is the only part of the code you need to look at - at least
nitially - everything else must be working if you only get the product not
found message.

msgbox "==>" & sProd & "<==="
res = Application.Match(sProd, _
Worksheets("ControlCentre").Range("C77:C1000"), 0)
If Not IsError(res) Then
With Worksheets("ControlCentre")
If IsNumeric(Target.Value) Then
Click to expand...


res + 76 is column ControlCentre!AR
Set rng2 = .Cells(res + 76, icol)
rng2.Value = rng2.Value + Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd
End If
Click to expand...

If C77:C1000 contain numbers, then that could be a problem, because you
are searching with a string. In otherwords "123" <> 123

Just a thought - I can't see your data.
 
P

Pat

If C77:C1000 contain numbers, then that could be a problem, because you
are searching with a string. In otherwords "123" <> 123
Click to expand...

Actually the formula in each cell in the range C77:C1000 begins with the
number "2" Any other sheet with product name also begin with the number "2"

="2"& " " &V104& " " &W104& " " &X104

The strange thing about the code is that similar code (but not exactly the
same) run on a sheet called PickingList! works just fine.

I will include it below:

Dim sProd As String
Dim icol As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range, Target As Range
Dim res As Variant
Set rng = Worksheets("PickingList").Range("J3")
res = Application.Match(ActiveSheet. _
Range("J23").Value, rng, 0)
If Not IsError(res) Then
icol = rng(res).Column
Else
MsgBox "Picking List not matched"
Exit Sub
End If
With ActiveSheet
Set rng1 = Nothing
On Error Resume Next
Set rng1 = .Range("R30:R1000"). _
SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "No Quantities in Picking List"
Exit Sub
End If
End With

For Each Target In rng1
sProd = Target.Parent.Cells(Target.Row, 12)

res = Application.Match(sProd, _
Worksheets("ControlCentre").Range("C77:C1000"), 0)
If Not IsError(res) Then
'With Worksheets("ControlCentre")
With Worksheets("PickingList")
If IsNumeric(Target.Value) Then
Set rng2 = .Cells(res + 29, icol)
rng2.Value = rng2.Value - Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd
End If
Next


Set rng = Worksheets("ControlCentre").Range("AR30")
res = Application.Match(ActiveSheet. _
Range("AC2").Value, rng, 0)
If Not IsError(res) Then
icol = rng(res).Column
Else
MsgBox "Picking List not matched"
Exit Sub
End If
With ActiveSheet
Set rng1 = Nothing
On Error Resume Next
Set rng1 = .Range("R30:R1000"). _
SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "No Quantities in Picking List"
Exit Sub
End If
End With

For Each Target In rng1
sProd = Target.Parent.Cells(Target.Row, 12)

res = Application.Match(sProd, _
Worksheets("ControlCentre").Range("C77:C1000"), 0)
If Not IsError(res) Then
With Worksheets("ControlCentre")
If IsNumeric(Target.Value) Then
Set rng2 = .Cells(res + 76, icol)
rng2.Value = rng2.Value + Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd
End If
Next


Regards
Pat
 
T

Tom Ogilvy

My best guess is that it isn't a coding problem - it is a data problem.
Putting up yards of code to look at will not solve that and you have too
much going on there for me to try to test if there are any logic or coding
problems - but it doesn't raise an error, so that is a good start. You need
to do some debugging. Isolate that small piece of code into another routine
and get it working there. See what is causing the problem. (again, I
suspect it is the data and not the logic)
 
P

Pat

A new day and a fresh look does wonders. You are correct in determining
that it is the data and not the code that is causing the problem. Thank you
for helping me along the road to pin-pointing the problem.

Cheers
Pat
 

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

Allow code to continue regardless 1
Recognise value not formula 4
Linking workbooks 2
Find 'Product not found' 2
With and End With issue 4
Identify product not found 1
End process at last row of data 3
Help with code 7

Top