Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognise value not formula
When the following code is executed it will behave as it should. The problem
arises when a value in ("R74:R1000") is returned from a formula. Does anyone know how can I get around this problem? Thanks in advance if you can help. Pat The code as follows: Private Sub BTStock_Click() 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("ControlCentre").Range("AL30") res = Application.Match(ActiveSheet. _ Range("R26").Value, Rng, 0) If Not IsError(res) Then icol = Rng(res).Column Else MsgBox "InvSter not matched" Exit Sub End If With ActiveSheet Set rng1 = Nothing On Error Resume Next Set rng1 = .Range("R74:R1000"). _ SpecialCells(xlConstants, xlNumbers) On Error GoTo 0 If rng1 Is Nothing Then MsgBox "No Quantities in InvSter" Exit Sub End If End With For Each Target In rng1 sProd = Target.Parent.Cells(Target.Row, 17) 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum function does recognise % | Excel Worksheet Functions | |||
Recognise text as date | Excel Worksheet Functions | |||
how do i get formula to recognise text as = to a number?(excel) | Excel Worksheet Functions | |||
let a sub recognise the name of the button that has been clicked. | Excel Discussion (Misc queries) | |||
XNPV - cant recognise dates | Excel Discussion (Misc queries) |