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