+ Reply to Thread
Results 1 to 10 of 10

Automate process

  1. #1
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    200

    Question Automate process

    Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.

    Example:
    A1 @ 31/01/07 = 50 therefore cell C1 = 50
    A1 @ 28/02/07 = 45 therefore cell C2 = 45
    and so on for 12 months

    what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.

    Could this be done by date formula?

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Hi,

    a small 'Close' event macro should do the trick, it will continually save A1 into C&Row()=month

    Please Login or Register  to view this content.
    Put that in the ThisWorkBook sheet in (Alt/F11) VB Editor.

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    200

    Cool

    many thanks - only problem is I am not too hot on macros / VB - is there no other way that data can be captured and made static or will I just have to get my macro thinking cap on?

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by martins
    many thanks - only problem is I am not too hot on macros / VB - is there no other way that data can be captured and made static or will I just have to get my macro thinking cap on?
    just press Alt/F11

    select ThisWorkBook ( from View, Project Explorer window - double-click -or- Rightmouse and View Code )

    paste the code into that sheet.

    ---

  5. #5
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    200
    so am I right in thinking that at present the figure from A1 will be captured in cell 2 (i.e. FEB being current month) and then on 1st march the figures will be captured in cell C3 and Cell 2 will then contain the last figure entered at end of Feb and remain static? and this process will continue through to cell C12 i.e. December?

    +will it matter if cell A1 contains a formula that totals figures from another column

    Many thanks for your help

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by martins
    so am I right in thinking that at present the figure from A1 will be captured in cell 2 (i.e. FEB being current month) and then on 1st march the figures will be captured in cell C3 and Cell 2 will then contain the last figure entered at end of Feb and remain static? and this process will continue through to cell C12 i.e. December?

    +will it matter if cell A1 contains a formula that totals figures from another column

    Many thanks for your help
    Hi,

    that is what is hoped for, and the .Value component does not care how the value was obtained.

    ---

  7. #7
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    200
    could you help a bit further - I actually have 2 columns collecting figures so instead of A1, I have C226 and C229 and the corresponding monthly cells are starting B241 (jan) and C241 (jan) respectively through to b252 and c252. Could you provide the macro for that scenario please.

  8. #8
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by martins
    could you help a bit further - I actually have 2 columns collecting figures so instead of A1, I have C226 and C229 and the corresponding monthly cells are starting B241 (jan) and C241 (jan) respectively through to b252 and c252. Could you provide the macro for that scenario please.
    Hi,

    for simplicity, you can repeat the line
    Please Login or Register  to view this content.
    with different addresses, alternately you can use the form
    Please Login or Register  to view this content.
    both mean the same thing, the Row increment is calculated into myMonth, so to save into C241 for Jan would be
    Please Login or Register  to view this content.
    etc.
    also, to save typing and because these are all sheet1
    Please Login or Register  to view this content.
    so you can just duplicate these lines and amend the addresses as required. (only one of the lines is required, they are different forms of the same instruction)

    hth
    ---
    Last edited by Bryan Hessey; 02-25-2007 at 05:59 AM.

  9. #9
    Forum Contributor
    Join Date
    02-16-2006
    Posts
    200

    Cool

    To achieve the required result I am entering the code as follows and this seems to work -

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim myMonth As Integer
    myMonth = Month(Now)
    Sheets("Schedule2007").Cells(240 + myMonth, 2).Value = Sheets("Schedule2007").Cells(226, 3).Value
    Sheets("Schedule2007").Cells(240 + myMonth, 3).Value = Sheets("Schedule2007").Cells(229, 3).Value
    End Sub

    This works fine however, something that I had forgotten until testing the macro is that within the work sheet I have to constantly add or delete rows therefore, my target cells references can go up or down. Is there any way around this or is it getting too complicated?

  10. #10
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by martins
    To achieve the required result I am entering the code as follows and this seems to work -

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim myMonth As Integer
    myMonth = Month(Now)
    Sheets("Schedule2007").Cells(240 + myMonth, 2).Value = Sheets("Schedule2007").Cells(226, 3).Value
    Sheets("Schedule2007").Cells(240 + myMonth, 3).Value = Sheets("Schedule2007").Cells(229, 3).Value
    End Sub

    This works fine however, something that I had forgotten until testing the macro is that within the work sheet I have to constantly add or delete rows therefore, my target cells references can go up or down. Is there any way around this or is it getting too complicated?
    Hi,

    not at all, providing there is some way to identify a row, ie, from VB you can use the Match( ) formula to find the row required for the 'from' and the 'to' rows and incorporate them into the code, also
    Please Login or Register  to view this content.
    is shorter and does the same thing.

    What is the identifier for the two rows?

    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1