Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to store variables of a single cell in a column?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to store variables of a single cell in a column?
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 -- Cheers Nigel "noyau" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to store variables of a single cell in a column?
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 "Nigel" wrote: 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 -- Cheers Nigel "noyau" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to store variables of a single cell in a column?
Is your sheet named Sheet1? If not, adjust the code.
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "noyau" wrote in message ... 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 "Nigel" wrote: 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 -- Cheers Nigel "noyau" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to store variables of a single cell in a column?
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? "Bob Phillips" wrote: Is your sheet named Sheet1? If not, adjust the code. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "noyau" wrote in message ... 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 "Nigel" wrote: 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 -- Cheers Nigel "noyau" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to store variables of a single cell in a column?
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. -- Regards, Tom Ogilvy "noyau" wrote in message ... 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? "Bob Phillips" wrote: Is your sheet named Sheet1? If not, adjust the code. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "noyau" wrote in message ... 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 "Nigel" wrote: 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 -- Cheers Nigel "noyau" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row & column portions of a cell as variables | Excel Discussion (Misc queries) | |||
How can you use IF looking at a column, not just a single cell? | Excel Worksheet Functions | |||
From single cell variables to a single column serie | New Users to Excel | |||
add a row or column to a single cell in exel | Excel Discussion (Misc queries) | |||
Store variables between XLA sessions | Excel Programming |