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

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

--
Regards,
Tom Ogilvy
"Pat" wrote in message
...
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

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


ControlCentre!AR30 contains the name "Plist Totals"

Set rng = Worksheets("ControlCentre").Range("AR30")


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


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


Target.Row 24 is column X in the Invoice! sheet and contain the names of
products

sProd = Target.Parent.Cells(Target.Row, 24)


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


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



Regards
Pat

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


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

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.

---
Regards,
Tom Ogilvy


"Pat" wrote in message
...
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


ControlCentre!AR30 contains the name "Plist Totals"

Set rng = Worksheets("ControlCentre").Range("AR30")


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


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


Target.Row 24 is column X in the Invoice! sheet and contain the names of
products

sProd = Target.Parent.Cells(Target.Row, 24)


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


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



Regards
Pat

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

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














"Tom Ogilvy" wrote in message
...
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


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

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.

---
Regards,
Tom Ogilvy


"Pat" wrote in message
...
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


ControlCentre!AR30 contains the name "Plist Totals"

Set rng = Worksheets("ControlCentre").Range("AR30")


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


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


Target.Row 24 is column X in the Invoice! sheet and contain the names of
products

sProd = Target.Parent.Cells(Target.Row, 24)


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


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



Regards
Pat

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)

--
Regards,
Tom Ogilvy

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

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














"Tom Ogilvy" wrote in message
...
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


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

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.

---
Regards,
Tom Ogilvy


"Pat" wrote in message
...
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


ControlCentre!AR30 contains the name "Plist Totals"

Set rng = Worksheets("ControlCentre").Range("AR30")


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


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


Target.Row 24 is column X in the Invoice! sheet and contain the names
of
products

sProd = Target.Parent.Cells(Target.Row, 24)


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


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



Regards
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


"Tom Ogilvy" wrote in message
...
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)

--
Regards,
Tom Ogilvy

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

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














"Tom Ogilvy" wrote in message
...
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


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

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.

---
Regards,
Tom Ogilvy


"Pat" wrote in message
...
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


ControlCentre!AR30 contains the name "Plist Totals"

Set rng = Worksheets("ControlCentre").Range("AR30")


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


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


Target.Row 24 is column X in the Invoice! sheet and contain the names
of
products

sProd = Target.Parent.Cells(Target.Row, 24)


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


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



Regards
Pat