+ Reply to Thread
Results 1 to 5 of 5

Editing a recorded macro to be useable on future data sets

  1. #1
    Registered User
    Join Date
    06-22-2012
    Location
    Shelton, Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    5

    Editing a recorded macro to be useable on future data sets

    On a monthly basis, I run several reports where the same editing formulas formatting and cross referrencing is required. So it makes sense that I would want to create macros to carry out these specific functions on a regular basis. I've done no VBA in the past so my preferred methodology would be to simply record a macro and have it be executable when I drop the new Data in. However, I seem to be running into the same problem when I attempt to do this. If I can figure out a simple way to correct this via VBA I could then significantly reduce my monthly tasks. The issue is that I get a sales report, and there are standard column headers but a varied number of Rows.

    IE
    Account number Sales Part Part Type
    222222 $1.00 22 Router

    What I want to do is insert a new column and every time router occurs insert the word "Network Product"
    A simple =if($D2="Router","Network Product", "non-Network Related")
    I would then double click the corner of that cell to have it fill in the rest of the Rows, with Network or non-Network Related.
    If I take the same data and run the macro it has no issue doing this.
    However when I run a different report that has 600 Rows rather then 588 the formula does not copy down Past 588, and in the interest of completly automated I wonder what the code string I could add in to the recorded macro via VBA to have it copy to the last row and no further.

    Can anyone offer some suggestions? Or perhaps the way to go is have the macro only add one rows formula and then have to continue to copy the formula manually?

    I appreciate any insight.

    Jason

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Lightbulb Re: Editing a recorded macro to be useable on future data sets

    Hello
    Not sure how your data is set up. But for example, if your 4 columns of 'Account number'; 'Sales'; 'Part' and 'Part Type', were in columns A:D Sheet1 and column E were empty, then the following code would insert your IF formulas to the correct number of rows automatically.

    This assumes there are 'Account numbers' with every record, and nothing below, as the LstRow variable finds the last non empty row up from the bottom of column A.

    Please Login or Register  to view this content.
    Hope this helps.
    DBY

  3. #3
    Registered User
    Join Date
    06-22-2012
    Location
    Shelton, Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Editing a recorded macro to be useable on future data sets

    So here is an example of my code:
    Sub Days_Open()
    '
    ' Days_Open Macro
    ' Days open
    '
    ' Keyboard Shortcut: Ctrl+Shift+D
    '
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
    Range("F2").Select
    Selection.NumberFormat = "General"
    Selection.AutoFill Destination:=Range("F2:F2401")
    Range("F2:F2401").Select
    End Sub


    Instead of marking it F2:F2401, I want it to run that formula down to the last row that holds data. could I simply change Everywhere it states ("F2:F2401") to ("F2:F" & LstRow)? And add a
    LstRow = .Range("A" & Rows.Count).End(xlUp).Row

    Obviously I'm looking to do more than just this one thing to the sheet, but I know once I grasp this I can apply it in all the different scenarios where I need to do this.

    Thanks,

    Jason

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Editing a recorded macro to be useable on future data sets

    Hi
    Sorry, I don't know what that Macro refers to. It's not the same as your original question, where you wanted an IF formula placed alongside your columns of text. If the rows you wish to count are in column A, then you should be able to use the LstRow variable in your range selections but without the 'With Statement':

    Please Login or Register  to view this content.
    Amend the sheet name to whatever it may be. You might have to declare the variable at the top of the code with:

    Please Login or Register  to view this content.
    DBY
    Last edited by DBY; 06-25-2012 at 01:09 PM.

  5. #5
    Registered User
    Join Date
    06-22-2012
    Location
    Shelton, Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Editing a recorded macro to be useable on future data sets

    Awesome thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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