+ Reply to Thread
Results 1 to 13 of 13

Do until macro with variable

  1. #1
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Do until macro with variable

    This is following on from a previous post.

    I have the following formula to copy a range of cells where the row depends on the value of another cell (in this case CN2,3,4...) which itself is determined by a match function.

    I am as far as this...


    Please Login or Register  to view this content.
    and need to keep doing this going down column CN until the cell is empty

    In the case of the attached spreadsheet for CN2,CN3,CN4, stopping when empty CN5 is reached. It can always start on CN2

    both the CN? values in the code should change after each iteration...
    i.e. 2nd iteration should be

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,225

    Re: Do until macro with variable

    Please Login or Register  to view this content.

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Do until macro with variable

    Your macro is calling a row number which is not the data row I think you are expecting.
    Seems to me it is out by 5 rows, this is easily fixed but this might be a more versitile solution if my interpretation of your needs is correct.

    1/. In CM2 put the figure you want to start with say 7161

    2/. In CM3
    Please Login or Register  to view this content.
    Drag/Fill Down as required

    3/. In CN2
    Please Login or Register  to view this content.
    Drag/Fill across to CY2
    Select CN2:CY2
    Drag Down as required

    4/. Format Column CY "dd.mm.yyyy-hh.mm.ss" (to match your formatting)

    This will return your data in 30 minute steps starting 13/07/2010 11:00:00 (i.e. 7161)

    To change the time step change CM3 and fill down as required.
    The data will update automatically

    To change the start time (Minutes) in CM2 , again the data will update automatically.

    Try the appended workbook

    Hope this helps.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Do until macro with variable

    Hi Bob, thanks for taking a look again....
    I couldn't get the code to work

    I inserted it in my full code as follows...

    Please Login or Register  to view this content.

    ...do I need to add something more to the macro code you provided? It should, if working correctly, populate cell CN2 to CW2 of sheet 2 with the appropriate cells from row 7166 (as defined by the original value in CN2), and so on for CN3 and CN4.

    I couldn't see any paste type function in the code so i tried adding some prior to the end with but couldn't get it to work.

    Any idea whats going wrong?

    Attached is what the workbook should end up looking like
    Attached Files Attached Files
    Last edited by *benj; 07-30-2010 at 10:11 AM.

  5. #5
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Do until macro with variable

    Hi Marco,

    thanks for the response.... the reason i need to write a macro rather than drag down code is because this will have to be done every day for a large amount of data where the match up of times will usually not be in such a regular 30 minute step.

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Do until macro with variable

    How is your sampling data imported?
    i.e in your original workbook the column containing the values 7166, 7196, and 7226

    Do you append to this list daily, or delete the data each time?

  7. #7
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Do until macro with variable

    it comes from a text file and another text type file.

    it changes every day.

    it is neccessary to run a match function to identify which row contains the matching time value, i.e. from which row to copy the cells of data.

    the row on sheet 1 which corresponds to the first value varies as does the time interval, sometimes irregularly

  8. #8
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Do until macro with variable

    it is a completely new workbook each time for each days data, hence the need for a macro

  9. #9
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,225

    Re: Do until macro with variable

    I don't understand why you are autofilling, but you don't need all of that code, this should do it for you

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Do until macro with variable

    Hi Bob, I'm getting a runtime error 424 when i run this macro on workbook 1 (attached above). It occurs on this line

    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    Any ideas?

  11. #11
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,225

    Re: Do until macro with variable

    Got me bud. I created it against your workbook and it worked fine here.

  12. #12
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Do until macro with variable

    thats perculiar... any clue what a runtime error 424 refers to?
    I use excel 2007 could there be any changes related to this

    I altered last row to 4 to force past this error and it immediately gave me another runtime error 424 on the next line

    " .Range("CN2").AutoFill .Range("CN2").Resize(LastRow - 1)"

    am i meant to be adding anything into this code to apply it to my spreadsheet?

  13. #13
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,225

    Re: Do until macro with variable

    I ran it against 2007 too.

    Error 424 is Object error, it is a generic error that could be one of many things.

+ 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