Print report from Listbox

[austenr]

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

[Jacob Hilderbrand]

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?



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

[austenr]

yes that is the idea

[Jacob Hilderbrand]

So will this macro work for you or do we need to make some adjustments?

[austenr]

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.

[Jacob Hilderbrand]

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.

[austenr]

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

[Jacob Hilderbrand]

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:


ActiveSheet.PrintOut

[austenr]

Hmmmm. Didn't print the headings. Here is the code:


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

[Jacob Hilderbrand]

Make Row = 2 like it initially was.

[austenr]

great got it. Thanks. Solved.