If I have quite a complex sheet set up that uses random numbers to lookup
into a series of tables and produce a series of results, with a
button/associated onclick event set up

what is the best (if any?) way to append various cell results to a new row
on a separate sheet, so that you have a running record of whichever cells
you've decided to record each time


so if I click the button and get random numbers 5, 11, 23, plus codes B, G,
plus text items "Bobs", "your" "uncle"

I'd want that storing in one row on a sheet, if I then clicked the button
again with new results, I'd want the new values on the next row down etc,

so "logging" the result of certain cells with each calculation

tks in advance

Assume you are saving the data in sheet X, on every button click
initiate a macro to copy the data to a dynamically named range which is
the next empty row in sheet X.

Or, if you want it in reverse sequence, have the macro insert a row at
the top of sheet X and then always copy the data to row 1, or insert
and copy at row 2 if you have headers in row 1.

HTH

DOR


--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490632

"DOR" wrote in message
...

Assume you are saving the data in sheet X, on every button click
initiate a macro to copy the data to a dynamically named range which is
the next empty row in sheet X.

Or, if you want it in reverse sequence, have the macro insert a row at
the top of sheet X and then always copy the data to row 1, or insert
and copy at row 2 if you have headers in row 1.

HTH

DOR


It does to an extent, but I have no idea how to do that. If you point me to
a link with some help on macro code (is it VBA?) I'll give it a go!

I'm uploading a simple spreadsheet that does something like you want.

I created a Count named range in Sheet1 A1

The random results are in B1:G1.

A1:G1 is named Results

Log is a dynamic range on sheet two that eseentilally resolves to the
next open row in sheet 2 (or to be more precise the cell in column A of
the next open row in sheet 2)

The macro, initiated by the button copies values from Results to log
and then adds one to Count, which automatically causes recalc, creating
another set of random numbers. Youcan set Count to whatever you want
initially, the macro just adds one to it.

You can see where the code is stored by pressing alt-F11 and then
looking in module 1 of the spreadsheet.

For those who may not be able to download the spreadsheet the code
follows:

Sub LogResults()
Application.ScreenUpdating = False
Range("Results").Copy
Range("Log").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Count") = Range("Count") + 1
Application.ScreenUpdating = True
End Sub

Hope it works for you.

Declan O'R


+-------------------------------------------------------------------+
|Filename: LogRandsData.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4097 |
+-------------------------------------------------------------------+

--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490632

"DOR" wrote in message
...

I'm uploading a simple spreadsheet that does something like you want.

I created a Count named range in Sheet1 A1

The random results are in B1:G1.

A1:G1 is named Results

Log is a dynamic range on sheet two that eseentilally resolves to the
next open row in sheet 2 (or to be more precise the cell in column A of
the next open row in sheet 2)

The macro, initiated by the button copies values from Results to log
and then adds one to Count, which automatically causes recalc, creating
another set of random numbers. Youcan set Count to whatever you want
initially, the macro just adds one to it.

You can see where the code is stored by pressing alt-F11 and then
looking in module 1 of the spreadsheet.

For those who may not be able to download the spreadsheet the code
follows:

Sub LogResults()
Application.ScreenUpdating = False
Range("Results").Copy
Range("Log").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Count") = Range("Count") + 1
Application.ScreenUpdating = True
End Sub

Hope it works for you.

Declan O'R


+-------------------------------------------------------------------+
|Filename: LogRandsData.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4097 |
+-------------------------------------------------------------------+

--
DOR
------------------------------------------------------------------------
DOR's Profile:
http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490632


Thanks Declan, will have a play wit it now!
cheers

"DOR" wrote in message
...

I'm uploading a simple spreadsheet that does something like you want.

I created a Count named range in Sheet1 A1

The random results are in B1:G1.

A1:G1 is named Results

Log is a dynamic range on sheet two that eseentilally resolves to the
next open row in sheet 2 (or to be more precise the cell in column A of
the next open row in sheet 2)

The macro, initiated by the button copies values from Results to log
and then adds one to Count, which automatically causes recalc, creating
another set of random numbers. Youcan set Count to whatever you want
initially, the macro just adds one to it.

You can see where the code is stored by pressing alt-F11 and then
looking in module 1 of the spreadsheet.

For those who may not be able to download the spreadsheet the code
follows:

Sub LogResults()
Application.ScreenUpdating = False
Range("Results").Copy
Range("Log").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Count") = Range("Count") + 1
Application.ScreenUpdating = True
End Sub

Hope it works for you.

Declan O'R


+-------------------------------------------------------------------+
|Filename: LogRandsData.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4097 |
+-------------------------------------------------------------------+

--
DOR
------------------------------------------------------------------------
DOR's Profile:
http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490632


Thanks, I almost have it working, just getting an error now
Runtime Error 1004 - range of object not found etc

I think it's because the button I have is on a different sheet to my log and
am not sure of syntax to log on different page

here is my button click code

Option Explicit
Private Sub CommandButton1_Click()
Application.Calculate
Application.ScreenUpdating = False
Range("Results").Copy
Range("Log").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Count") = Range("Count") + 1
Application.ScreenUpdating = True
End Sub


I think it probably just needs something like Name of Sheet!"Results"
etc.... but not sure how to do it properly

I think I have two problems - not quite understanding the difference in
having code on a module and on a sheet code, and also the syntax for
named ranges seems to be wrong, or I did something wrong!

Hope someone can help!


+-------------------------------------------------------------------+
|Filename: Yorkshire Play Calls 2003.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4098 |
+-------------------------------------------------------------------+

--
ldsdbomber
------------------------------------------------------------------------
ldsdbomber's Profile: http://www.excelforum.com/member.php...o&userid=29368
View this thread: http://www.excelforum.com/showthread...hreadid=490632

I'm not sure why some things were not working and really didn't have
time to do too much debugging, but I used some code I recorded to make
it work (that's usually a safe way! - and a good way to learn, maybe
not the most elegant code, since the macro recorder generates some
inefficient code). Nevertheless, I changed a few other things and it
works in your spreadsheet now, with the Click count also showing in the
log.

You were right about one of the problems; it was that you had the
button and the Results range on different pages. I had assumed you
would put the Results range on the same page as the button. Your Log
range as also not defined as a dynamic range.

It is logging now. However, the values in the Results range are not
changing, so you may need to look at your formulas in that range; they
should be picking up whatever you want to log.

Let me know if it works for you.

Regards

DOR


+-------------------------------------------------------------------+
|Filename: Copy of Yorkshire Play Calls 2003.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4100 |
+-------------------------------------------------------------------+

--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490632

Lee

I posted a resonse with corrections to your sheet that made it run ok,
and I anm wondering if it worked for you? Maybe you haven't noticed it
because my reply was to a message posted by someone other than you.

Let me know if it is ok. If it is not, you email me direct via Google
Groups.

Declan O'R