How to store variables of a single cell in a column?

[noyau]

I am after to store the "daily variables of a single cell" into a column and
form a serie.
Every day, the value in the cell (say A1) is altered. I want to extract that
value from A1 to any other place to get the history of the A1. The date of
the data will be attached to the value. Is there a way?

In the below logic we get random numbers for lotterie, and every draw is
written to the next row. How does it come? This is in fact what I am looking
for but I was enable to extract the good fo the program for me.

Happy new year to everyone...

Function RandLotto(Bottom As Integer, Top As Integer, _
Amount As Integer) As String

'From: http://www.ozgrid.com/VBA/RandomNumbers.htm
'by Dave Hawley & JE McGimpsey

Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer

Application.Volatile

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i
For i = Top To Bottom + 1 Step -1
r = Int(Rnd() * (i - Bottom + 1)) + Bottom
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
For i = Bottom To Bottom + Amount - 1
RandLotto = RandLotto & " " & iArr(i)
Next i
RandLotto = Trim(RandLotto)
End Function

Sub Draw()
Dim iMyNumber As Long
For iMyNumber = 1 To 200
Calculate
Next iMyNumber
Range("G65536").End(xlUp)(2).Value = Range("B2").Value
End Sub

Sub Reset()
Sheets("Draw").Select
Columns("G:G").ClearContents
Range("G1").Select
End Sub

 

[Nigel]

This code will take the value in A1 and store it in the next empty row in
column E, with the current date in column D

Sub ArchiveA1()
Dim xlr As Long
With Sheets("Sheet1")
.Range("D1") = "Date"
.Range("E1") = "Value"
xlr = .Cells(Rows.Count, "D").End(xlUp).Row + 1
.Cells(xlr, 4) = Format(Date, "dd/mm/yyyy")
.Cells(xlr, 5) = .Range("A1")
End With
End Sub

 

[noyau]

Thanks a lot. That should be exactly what I need but I end up with a run time
error 9. Have no idea what may be wrong.
"It's hard to be a rookie"

nyn

 

[Bob Phillips]

Is your sheet named Sheet1? If not, adjust the code.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[noyau]

That's it, the excel I am working with is not in english, there comes the
problem.
How to further to run the macro automatically whenever the value in cell a1
is altered? The problem is that the new value may be the same of the previous
one.
That can be done with an if statement or a timestamp like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Range("a1:a20"), Target) Is Nothing Then
If Target.Cells.Count = 1 Then
With Target
If IsNumeric(.Value) And .Value > 0 Then
Target.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
Target.Offset(0, 1).Value = ""
End If
End With
End If
End If
End Sub

is necessary?

 

[Tom Ogilvy]

Since your running a macro to generate the numbers, then why not just have
the macro write the results as they are generated. It appears that is what
you code does - so it is unclear what the question is.

this code generates 200 randome numbers, then saves the last one generated
I assume.

Sub Draw()
Dim iMyNumber As Long
For iMyNumber = 1 To 200
Calculate
Next iMyNumber
Range("G65536").End(xlUp)(2).Value = Range("B2").Value
End Sub

So what do you want changed.