Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing a rng selected from a listbox
Hi there,
I need some more help transposing the following the following: I'v pasted the whole part so you can see how i am getting my info: Set rng = Range(progListBox.RowSource).Columns(1).Cells cl = rng.Offset(progListBox.ListIndex, 0).Row With Workbooks("bonddb.xls").Worksheets("database") Load: Range("Issr") = .Cells(cl, 1) Range("Issr_local") = .Cells(cl, 2) Range("Principal") = .Cells(cl, 4) Range("Term") = .Cells(cl, 7) *** .Columns("EE:GL").Rows(cl).Copy _ Destination:=Workbooks("bsapp_v2.xls").Worksheets( "bsmain").Rows(33).Columns("C" *** End With *** is the code that i need to transposed from a row to a column whe it is pasted. I have tried many different combos of the paste specia function and rearranging things but all i am able to generate ar errors. I hope my question makes sense. Thanks in adv for your help. Jo -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing a rng selected from a listbox
.Columns("EE:GL").Rows(cl).Copy _
Workbooks("bsapp_v2.xls").Worksheets("bsmain").Cel ls("C33"). _ PasteSpecial xlPasteAll, Transpose:=True -- Regards, Tom Ogilvy "JoeH " wrote in message ... Hi there, I need some more help transposing the following the following: I've pasted the whole part so you can see how i am getting my info: Set rng = Range(progListBox.RowSource).Columns(1).Cells cl = rng.Offset(progListBox.ListIndex, 0).Row With Workbooks("bonddb.xls").Worksheets("database") Load: Range("Issr") = .Cells(cl, 1) Range("Issr_local") = .Cells(cl, 2) Range("Principal") = .Cells(cl, 4) Range("Term") = .Cells(cl, 7) *** .Columns("EE:GL").Rows(cl).Copy _ Destination:=Workbooks("bsapp_v2.xls").Worksheets( "bsmain").Rows(33).Columns ("C") *** End With *** is the code that i need to transposed from a row to a column when it is pasted. I have tried many different combos of the paste special function and rearranging things but all i am able to generate are errors. I hope my question makes sense. Thanks in adv for your help. Joe --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing a rng selected from a listbox
Tom,
I used what you put in and got an expected: end of statement error s i put parens () around the xlpasteall, transpose and deleted the semico : replayed and resulted in a type mismatch error. if it helps any m VBA ver is listed as 6.3 Thanks for the quick reply Sorry to be such a bother Jo -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing a rng selected from a listbox
I edited you code, so I didn't get it completely cleaned up.
I neglected to delete the line continuation character on the end of the copy command and I had Cells("C33") with the cells left over from the original. It should have been changed to Range("C33") so the correction should have been .Columns("EE:GL").Rows(cl).Copy Workbooks("bsapp_v2.xls").Worksheets("bsmain").Ran ge("C33"). _ PasteSpecial xlPasteAll, Transpose:=True Just to give you a sense of reassurance, this worked fine: Sub AABBCC() cl = 100 With Workbooks("bonddb.xls").Worksheets("database") .Columns("EE:GL").Rows(cl).Copy End With Workbooks("bsapp_v2.xls").Worksheets("bsmain").Ran ge("C33"). _ PasteSpecial xlPasteAll, Transpose:=True End Sub -- Regards, Tom Ogilvy "JoeH " wrote in message ... Tom, I used what you put in and got an expected: end of statement error so i put parens () around the xlpasteall, transpose and deleted the semicol : replayed and resulted in a type mismatch error. if it helps any my VBA ver is listed as 6.3 Thanks for the quick reply Sorry to be such a bother Joe --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing a rng selected from a listbox
!!!!! Thanks
Tom, Where do i send the check :) Sometimes i really hate being a novice. But with your help lately I'l be able to figure this out alot quicker. Thanks again Joe Helferic -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set the selected state of an embedded ActiveX listbox | Excel Discussion (Misc queries) | |||
Listbox - write newly selected item back to the worksheet | Excel Programming | |||
Count number of selected item in Listbox | Excel Programming | |||
How to make a new table based on selected result in listbox | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |