Recognise value not formula

[Pat]

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

 

[Vasant Nanavati]

Would you care to explain what the code is designed to do and what you mean
by "the problem arises"?

 

[Pat]

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

 

[Dave Peterson]

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

 

[Patrick Glass]

Hi Dave,
Just sending you a word of thanks for sorting out the problem, it work
beautifully.


Pat