+ Reply to Thread
Results 1 to 6 of 6

Add IF Statement formula to cells in varying range and paste to used range

  1. #1
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Orlando, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    251

    Add IF Statement formula to cells in varying range and paste to used range

    I am writing a VBA script, and all parts of it work perfectly (pats self on the back). Now I need to add one more section to it.

    Data range is columns A - M, headers in row 1, data begins on row 2. The number of rows varies each time this report is run, but there is always a value in column A if there is data in that row, so Column A is my variable for this determination. Basically if I was using a traditional formula, I would write it up as:

    Please Login or Register  to view this content.
    Column A is a unique ID # for each person. Column C, which lists the person's name is the variable to determine whether it is Automated or Manual. If the cell is blank, then Column M must reflect "Manual", however if column M is populated with a value, the "Automated" must display. I need this formula to be copied down to all cells within Column M that have a value in Column A. Once that is done, I need to copy and paste as values.

    Now I attempted to do this myself, and got an error, Compile Error: Expected: End of Statement. I am still relatively new to VBA, and this particular task I have never attempted in VBA... double trouble.

    Please Login or Register  to view this content.
    ~*~ Sherry ~*~
    Orlando, FL

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Add IF Statement formula to cells in varying range and paste to used range

    Does this help?

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Orlando, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    251

    Re: Add IF Statement formula to cells in varying range and paste to used range

    John----

    That appears to work fine, however since I am building this for another user, I am trying to be able to avoid having them run multiple macros. I attempted to call the macro at the end of my previous macro, however it did not work right (it overwrote the Header in cell N1 with "Automated", entered "automated in cell N2, which is correct, and then all the other rows in this column are blank, however column A is populated until row 1547. How do I add this to my existing macro code. So you can "see what I am saying... this is my initial code, and then your code, which I am calling

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Add IF Statement formula to cells in varying range and paste to used range

    Change to:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Orlando, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    251

    Re: Add IF Statement formula to cells in varying range and paste to used range

    wooohooo works great! thanks so much... sorry for the delay in responding, too many meetings! Appreciate all your help!

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Add IF Statement formula to cells in varying range and paste to used range

    You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How Run Formula varying the Range
    By rcprito in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2014, 10:17 AM
  2. [SOLVED] Macro - Create / Copy / Paste Formula every 5 rows for varying range
    By Jill Pleau in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-29-2014, 12:31 PM
  3. If/Then statement to copy/paste range of cells
    By lacticacidbath in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2013, 11:50 AM
  4. varying a range in a formula
    By Justme47 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2009, 10:09 AM
  5. [SOLVED] Select a varying range of text and paste into a cell
    By JesterPrime in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2005, 07:05 AM

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