Consulting

Results 1 to 11 of 11

Thread: Solved: Print report from Listbox

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location

    Solved: Print report from Listbox

    Currently I have a macro that lets the user choose a company and the information is then populated into the corresponding fields. The data is stored in 1 column. I would like to be able to let the user create a report that will print the entire list horizontally. There are blank lines seperating each companies info. Can anyone lend a hand to help me get started. File is attached. Thanks to everyone for their help.

    forgot to attach the file
    Peace of mind is found in some of the strangest places.

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You can create a loop to re-arrange the data. Now do you want to put the data for each company on its own row?

    [vba]
    LastRow = Range("A65536").End(xlUp).Row
    Row = 2
    For i = 1 To LastRow Step 6
    Range("C" & Row).Value = Range("B" & i).Text
    Range("D" & Row).Value = Range("B" & i + 1).Text
    Range("E" & Row).Value = Range("B" & i + 2).Text
    Range("F" & Row).Value = Range("B" & i + 3).Text
    Range("G" & Row).Value = Range("B" & i + 4).Text
    Row = Row + 1
    Next i
    [/vba]

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    yes that is the idea
    Peace of mind is found in some of the strangest places.

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    So will this macro work for you or do we need to make some adjustments?

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    That should work. Thanks. I have another question. Could you break down what is happening in this formula?

    =ISNA(Match(E3,$I$3:$i$14,False))

    I am trying to compare two columns of data to see if there is a match. Here is what I think. Is it comparing what is in cell E3 to the range of data in column I all the way to row 14? It would put True in the cell the formula is in else would return False.
    Peace of mind is found in some of the strangest places.

  6. #6
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Match will return the position in the array that the value is in. So it will look in I3:I14 to match the value in E3. If there is a match you will get the relative position (I3 is position 1, I4 is position 2 etc.)

    If there is no match you will get an #N/A error.

    So then the ISNA function evaluates the result from the Match function.

    If there is no match you will get TRUE. If there is a match you will get FALSE.

  7. #7
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Hey that helps. That formula has always intimidated me a little. The macro does a good job assembling the report. A couple of things:


    How can I get headings to print from my list in col A. Also can it just be sent to a printer after it is formatted with headings? Thanks
    Peace of mind is found in some of the strangest places.

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    I started at Row 2 so you can use Row 1 for your headers.

    Range("C1").Value = ...

    When you want to print it, you can use this line of code:
    [vba]
    ActiveSheet.PrintOut[/vba]

  9. #9
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Hmmmm. Didn't print the headings. Here is the code:

    [VBA] Public Sub PrintMtvGroupList()
    LastRow = Range("A65536").End(xlUp).Row
    Row = 1
    Range("C1").Value = "Group Name"
    Range("D1").Value = "Bill Rep"
    Range("E1").Value = "Extension"
    Range("F1").Value = "Group Number"
    Range("G1").Value = "Back Up"
    For i = 1 To LastRow Step 6
    Range("C" & Row).Value = Range("B" & i).Text
    Range("D" & Row).Value = Range("B" & i + 1).Text
    Range("E" & Row).Value = Range("B" & i + 2).Text
    Range("F" & Row).Value = Range("B" & i + 3).Text
    Range("G" & Row).Value = Range("B" & i + 4).Text
    Row = Row + 1
    Next i
    Columns("C:G").PrintOut
    Columns("C:G").Clear
    End Sub [/VBA]
    Peace of mind is found in some of the strangest places.

  10. #10
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Make Row = 2 like it initially was.

  11. #11
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    great got it. Thanks. Solved.
    Peace of mind is found in some of the strangest places.

Posting Permissions

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