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