+ Reply to Thread
Results 1 to 14 of 14

VBA Loops / IF

  1. #1
    Registered User
    Join Date
    05-21-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    25

    VBA Loops / IF

    VBA Loops / IF



    This is likely to be really simple for many, but I can't seem to get my head around it. I have the below which works almost perfectly.

    I basically have a list of 50 invoices in the A column and a invoice template on another sheet. The invoice template will pull the information for A2 so all the information is in the invoice, then save. As the information is pulled only from A2, I need to then delete that row so it picks up the next item in the list.

    Currently, because im deleted a row it is skipping numbers, for example, if I had a list of 1-10 it would save, 1,3,5,7,9 and complete.

    Any help or edits would be appreciated


    Please Login or Register  to view this content.
    Last edited by ddbroadhead92; 07-28-2016 at 10:06 AM.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Loops / IF

    Edit: CODE tags added, thanks.
    Last edited by Olly; 07-28-2016 at 10:10 AM.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    05-21-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    25

    Re: VBA Loops / IF

    Apologies I hadn't realised. I have now changed this.

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Loops / IF

    I'm a little confused by how your workbook actually works. If you delete row 2, then surely the cell references in the "Template Re-design" worksheet which refer to "Addresses" row 2 no longer work?

    Perhaps you could attach a sample workbook.... Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    05-21-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    25

    Re: VBA Loops / IF

    It uses the =indirect function to it always references a2 and the formula isn't lost. I was hoping it would be a simple fix and there was a function which did something similar to

    End if
    Next x (-1 row)

    OR something like

    End if
    Start x from beginning or loop from top.

    As I say the macro does almost everything perfectly its just this one snag. Would take a bit of time to pull together the information requested, but if still necessary I could do this?

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Loops / IF

    I see.

    In that case, try something like:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-21-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    25

    Re: VBA Loops / IF

    It doesn't seem to like it. To explain step by step:

    > There is a list of invoices which would vary in length.
    > The macro identifies the first invoice number in A2 which is currently used to pull through all the desired information required on the invoice template
    > The template is then save with the Invoice number as the file name
    > It now deletes the 2nd row where the original invoice was stored so the 2nd invoice number is now pulled through into the template now the 1st has gone
    > This process repeats until there are no invoices left.

  8. #8
    Registered User
    Join Date
    05-21-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    25

    Re: VBA Loops / IF

    Quote Originally Posted by Olly View Post
    I'm a little confused by how your workbook actually works. If you delete row 2, then surely the cell references in the "Template Re-design" worksheet which refer to "Addresses" row 2 no longer work?

    Perhaps you could attach a sample workbook.... Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Im a complete noob at using this site, I can't seem to find how to upload the workbook :')

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Loops / IF

    Your step by step explanation is as I understood it.

    Quote Originally Posted by ddbroadhead92 View Post
    It doesn't seem to like it.
    Can you explain in more detail?! Is there an error (and if so, which error, on which line) ? Is there no error, but the result is not as expected?

  10. #10
    Registered User
    Join Date
    05-21-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    25

    Re: VBA Loops / IF

    Quote Originally Posted by Olly View Post
    Your step by step explanation is as I understood it.



    Can you explain in more detail?! Is there an error (and if so, which error, on which line) ? Is there no error, but the result is not as expected?
    As soon as I run it, it comes up with the following error

    Run-time error
    '-2147024773 (8007007b)':
    document not saved

  11. #11
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Loops / IF

    Quote Originally Posted by ddbroadhead92 View Post
    As soon as I run it, it comes up with the following error

    Run-time error
    '-2147024773 (8007007b)':
    document not saved
    Try stepping through the code, using F8. Looks like it's failing on this line:
    Please Login or Register  to view this content.
    Which was copied from your original macro...


  12. #12
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Loops / IF

    Here's a sample attachment, which does exactly as you specified...

    The only line I have modified for testing is:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-21-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    25

    Re: VBA Loops / IF

    I copied in the section from my own workbook and it worked fine. I may have typed over something in error when posting on this page.

    On initial testing, it looks great!! Some to work just as requested!!

    I must admit, after numerous times of reading over your macro I still can't figure out how it works! Something for me to learn in the future Thankyou for you help!

  14. #14
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA Loops / IF

    How it works?

    The WHILE... WEND loop looks at cell "A2" on sheet addresses, and repeats the loop until that cell is empty.

    The loop exports the invoice worksheet as PDF, then deletes row 2 of Addresses.

    Make sense?!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. YUou can also 'Add Reputation' to those who have helped you. Thanks.

+ 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. VBA loops
    By Malowle in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-13-2012, 05:30 PM
  2. VBA Loops
    By jeskit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2011, 06:58 AM
  3. VBA Loops
    By jeskit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2011, 09:19 AM
  4. VBA Loops
    By jeskit in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-24-2011, 10:26 AM
  5. Help with Loops
    By manojparmar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2006, 05:28 AM
  6. [SOLVED] Loops & Ifs
    By Shorty in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-04-2006, 07:10 AM
  7. Loops
    By David in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-10-2006, 07:00 PM

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