Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Product not found error
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to lookup product name and add qty's of all that are found | Excel Worksheet Functions | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Error 1706. No valid source could be found for the product Microso | Excel Discussion (Misc queries) | |||
Find 'Product not found' | Excel Programming | |||
Identify product not found | Excel Programming |