additon of cel imput automaticaly

[Mario]

hi
i have the following problem but no solution,

i want to format the cells B2:R99 as follow
for example B4 (like all others) if u enter a number it should add it
automatically for example:

Step1: input in B4 of 4 - output: 4 - algorithmus in formulr bar:
4
Step1: input in B4 of 5 - output: 9 - algorithmus in formulr bar:
=4+5
Step1: input in B4 of 3 - output 12 -algorithmus in formulr bar =
4+5+3
Step1: input in B4 of -4 output 8 - algorithmus in formulr bar
=4+5+3-4


i want to create a material list for a project for others, but this list
contains of 500 positions and 5 tables
but i want these data to be pursue-able . i am not really good in makro
programming, i have an additions algorithmus makro witch adds but it does
not give the algorithmus in the formular bar just the vallue of the output,
(for example only vallue 9 instead of '=4+5+7-7')

hope someone could help me

kind regards

mario

 

[J.E. McGimpsey]

one way:

Put this in the worksheet code module (right-click on the worksheet
tab, choose View Code, paste the code in the window that opens,
then click the XL icon on the toolbar to return to XL):


Option Explicit
Dim gvOldFormula As Variant

Private Sub Worksheet_Activate()
Dim i As Long
Dim j As Integer
With Range("B2:R99")
ReDim gvOldFormula(1 To .Rows.Count, 1 To .Columns.Count)
For i = 1 To UBound(gvOldFormula, 1)
For j = 1 To UBound(gvOldFormula, 2)
If .Cells(i, j).HasFormula Then
gvOldFormula(i, j) = .Formula
Else
gvOldFormula(i, j) = "="
End If
Next j
Next i
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("B2:R99")) Is Nothing Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = gvOldFormula(.Row - 1, .Column - 1) & _
"+" & .Value
Application.EnableEvents = True
gvOldFormula(.Row - 1, .Column - 1) = .Formula
End If
End If
End With
End Sub

 

[Mario]

hi

sry i got an error here

..Value = gvOldFormula(.Row - 1, .Column - 1) & _
"+" & .Value

any tips??