Results 1 to 9 of 9

Thread: VBA Issue help required please

  1. #1

    VBA Issue help required please



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I have been asked by a colleague for some help with a task and I am finding it rather difficult and know someone will have the answer, I'd love for you to share it with me please.

    The workbook contains the following setup

    Sheet 1 which contains multiple rows of data (the amount of rows of data will vary), address, qty, phone number, special instructions etc

    Sheet 2 is a pre created delivery note.

    I want to be able to do the following:

    Press the print button (print button has been added to sheet 2) and sheet 2 will populate various data from cells on sheet 1 to compile a delivery note and print 2 copies. Once printed I want the code to look at the next line to see if there is any data if so, populate the next row of information onto the delivery note and print 2 copies. I want this to repeat all the way down until no more data is found. The print button will print from the default printer.

    Someone please help, its been about 8 years since I last used VBA and am seriously struggling?

    I have an example of the file which I can either post to this thread or send directly for you to look at.

    Thanks in advance.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Please post the workbook to the forum.

    Below the quick reply pane, click go advanced, scroll down and click manage attachments, click add files to browse to your file.

    Looks like somebody offered to help on another site but you didn't bother to post the file.
    Last edited by NoS; 2015-07-23 at 05:40 AM. Reason: added last line

  3. #3
    File uploaded, thanks in advance for help if you can provide it.
    Attached Files Attached Files

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    First, you need to get rid of those merged cells, cosmetically they may look nice but they should be avoided like the plague.
    Instead of Merge and Center, select the cells that would be merged, go to format cells and in Horizontal alignment use center across selection.

    See if this helps you out.
    Delivery Note Example.xlsm

  5. #5
    Hi NoS

    I understand your comments ref the merge and center function. I will test a little later on when I get to the office

    thanks again for having a go at helping me out, I will let you know how I get on

  6. #6
    Hi again all,

    Just wanted to say thank you so much for your help and assistance it worked like a charm

    My colleague was overjoyed at the result and I promise I didn't take the credit, i told my colleague that this forum had helped me out

    again thanks, I'm sure I'll be on here again after more help but in the meantime rest easy knowing you have made someone very happy

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hey, that's great, glad I could help.
    Thanks for the feedback.

  8. #8
    Hi Nos,

    You helped me out with this a couple of weeks ago which was a great help and I am after some more advice if possible?

    The file has been tweaked somewhat due to operational demands and now it doesn't appear to be working correctly.

    When clicking the print button it now throws the data off all over the place or misses some data off completely, can you help me please?

    I have reattached the file in hope you can check it out for me (I'm now on Version 5)

    Here is hoping
    Attached Files Attached Files

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    You've changed the headers and columns of your "Directs" sheet.
    You need only adjust the column portion of .Offset(rows, columns) to get the values you want.

    Assuming your new column A will have something in it on every row used, the For Each cel part of the macro can remain as is.
    The values you want entered in the cells on "Delivery Note" are Offset from the A column.
    The first number is the number of rows to offset. Zero is entered because we don't want any row offset, we want to use the same row.
    So cel.Offset(0, 1) would be referring to same row, column B
    cel.Offset(0, 2) would be same row, column C
    cel.Offset(0, 3) would be same row, column D
    etc., etc.
    continuing this your Subbie would be cel.Offset(0, 16) which would be same row, column Q.
    Attached Files Attached Files

Posting Permissions

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