Excel Help

[U.F.O]

I would like to record the value of the cell A1 everytime it changes,
i have the folowing so far but it does not work from a formular.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("B65536").End(xlUp).Offset(1, 0).Value = Target
End Sub

any ideas thanks

[osknows]

Hello,

How about this...

Code: Select all

Option Explicit

Dim CellA1 As String

Private Sub Worksheet_Change(ByVal Target As Range)
If CellA1 <> Target.Cells(1, 1).Value Then
    CellA1 = Target.Cells(1, 1).Value
    Range("B65536").End(xlUp).Offset(1, 0).Value = CellA1
End If
End Sub

[U.F.O]

Thanks thats nearly what i need but
it still only works if you manually type a number to cell A1

want i need is to be able to have it work when A1 is the sum of another cell
if that makes sense.

might just be that im doing something wrong

[U.F.O]

If it helps, what i am trying to do
is record varius data, matched amounts, odds ect
everytime they change so i can make graphs and things out of the data .

[osknows]

Ah I understand now..

Try this

Code: Select all

Option Explicit

Dim CellA1 As String

Private Sub Worksheet_Calculate()
If CellA1 <> Cells(1, 1).Value Then
    CellA1 = Cells(1, 1).Value
    Range("B65536").End(xlUp).Offset(1, 0).Value = CellA1
End If
End Sub

[U.F.O]

Yes that works great thanks

[U.F.O]

Ok so another problem ,what i need to do now is
record some other cells values at the same time,

so say i had data in cells a1,a2,a3,a4,a5
i would like that copied into colums b, c, d, e, & f ,
everytime the Cell A1 value had changed.


thought i would have been able to do it myself but it appears not
thanks

[osknows]

Try this...

Code: Select all

Option Explicit

Dim CellA1 As String

Private Sub Worksheet_Calculate()
Dim varArray() As Variant
If CellA1 <> Cells(1, 1).Value Then
    CellA1 = Cells(1, 1).Value
    varArray = WorksheetFunction.Transpose(Range("A1:A5").Value)
    Range("B65536").End(xlUp).Offset(1, 0).Resize(1, 5).Value = varArray
End If
End Sub

[U.F.O]

thats exactly what i was looking for thanks a lot

would have taken me weeks to work out without help