Try replacing the first routine with the below, then
Select a different sheet, then come back to this sheet and try again.
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
gvOldFormula(i, j) = .Cells(i, j).Formula
Else
'gvOldFormula(i, j) = "="
gvOldFormula(i, j) = "=" & Trim(.Cells(i, j).Value)
End If
Next j
Next i
End With
End Sub
or
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)
With .Cells(i,j)
If .HasFormula Then
gvOldFormula(i, j) = .Formula
Else
'gvOldFormula(i, j) = "="
gvOldFormula(i,j) = "=" & trim(.value)
End If
End With
Next j
Next i
End With
End Sub
--
Regards,
Tom Ogilvy
Mario <(E-Mail Removed)> wrote in message
news:3fae7c08$0$33274$(E-Mail Removed)...
> hi
>
> sry i got an error here
>
> .Value = gvOldFormula(.Row - 1, .Column - 1) & _
> "+" & .Value
>
> any tips??
>
>
> "J.E. McGimpsey" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:jemcgimpsey-(E-Mail Removed)...
> > 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
> >
> >
> > In article
> > <3fae2f80$0$23152$(E-Mail Removed)>,
> > "Mario" <(E-Mail Removed)> wrote:
> >
> > > 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
> > >
> > >
>
>
|
Similar Threads
