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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognise value not formula
Would you care to explain what the code is designed to do and what you mean
by "the problem arises"? :-) -- Vasant "Pat" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognise value not formula
The code is designed to record information that changes on one sheet and
retain it on another sheet. Basically what I have is a sheet "InvSter" which produces invoices. Because excel is not a relational data system I need to record the items on each invoice that is printed. So when items are entered into the invoice sheet "InvSter" and then ready to be printed a command button is executed with the code as shown earlier the products quantities are then entered into another sheet "controlcentre " There is no problem with code only "the problem arises"? if a formula is used as the reference: Set rng1 = .Range("R74:R1000") it will not record the value returned by the formula. If there is no formula in Set rng1 = ..Range("R74:R1000") only a value all is fine. I am getting around this for the moment by including code that will copy and paste special the value in ("R74:R1000") first before the main body code is run, as shown below. Range("R28:R1180").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("R28").Select Application.CutCopyMode = False Range("J28").Select "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Would you care to explain what the code is designed to do and what you mean by "the problem arises"? :-) -- Vasant "Pat" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognise value not formula
You could look for both constants and formulas in that range:
Add a couple of Dim's at the top: Dim rng1F As Range Dim rng1C As Range Then plop this portion in the middle (and delete the corresponding portion): 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 InvSter" Exit Sub End If End With Pat wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognise value not formula
Hi Dave, Just sending you a word of thanks for sorting out the problem, it work beautifully. Pat *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
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) |