Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Sheet
I have a spreadsheet that contains Part Numbers and Supplier Data and other
information. The data is in the following format: Col A Col B Col C:? Part # Supplier Other Information in Rows c:? Could be aa or ZA 123 ABC 123 DEF 123 GHI 456 ABC 456 DEF I would like to merge the data to look like this: Col A Col B Other INformation C:? 123 ABC DEF GHI 456 ABC DEF One Part number may have 1 suppliers or 100 suppliers. The data in Col C:? is supplier specific so it would need to remain on the same row as the supplier. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Sheet
Hi C,
You can try this: Public Sub SplitOnPartnumber() Do If StrComp(ActiveCell.Value, ActiveCell.Offset(-1, 0).Value, vbTextCompare) < 0 Then ActiveCell.EntireRow.Insert xlShiftDown ActiveCell.Value = ActiveCell.Offset(1, 0).Value ActiveCell.Font.Color = ActiveCell.Offset(1, 0).Font.Color Else ActiveCell.Font.Color = ActiveCell.Interior.Color End If ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell) End Sub Select the cell with the first part# and then start this macro. HTH, Wouter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Sheet
Hi Wouter,
I tried this but get a syntax error on the first If statement. Any recommendations? "Wouter HM" wrote: Hi C, You can try this: Public Sub SplitOnPartnumber() Do If StrComp(ActiveCell.Value, ActiveCell.Offset(-1, 0).Value, vbTextCompare) < 0 Then ActiveCell.EntireRow.Insert xlShiftDown ActiveCell.Value = ActiveCell.Offset(1, 0).Value ActiveCell.Font.Color = ActiveCell.Offset(1, 0).Font.Color Else ActiveCell.Font.Color = ActiveCell.Interior.Color End If ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell) End Sub Select the cell with the first part# and then start this macro. HTH, Wouter . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Sheet
Hi C,
Please check in your VBE Tools -- References. On top of the list thses four should be checked: - Visual Basic for Applications - Microsoft Excel n Object Library - OLE Automation - Microsoft Office n Object Library Which version of Excel ar u using? Wouter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Sheet
Hi Wouter,
I checked and indeed these four are enabled/checked. I am using Excel 2003 SP3 Professional edition. I haven't had a chance to get back to this until today. Was hoping to get your insight on they syntax error. I am a newbie with this stuff and don't know what might be causing this. I copied your post and get the error. Thanks for your help, "Wouter HM" wrote: Hi C, Please check in your VBE Tools -- References. On top of the list thses four should be checked: - Visual Basic for Applications - Microsoft Excel n Object Library - OLE Automation - Microsoft Office n Object Library Which version of Excel ar u using? Wouter . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Sheet
Hi C,
This part of my code should be on one line: If StrComp(ActiveCell.Value, ActiveCell.Offset(-1, 0).Value, vbTextCompare) < 0 Then Optionally you can use: If StrComp(ActiveCell.Value, _ ActiveCell.Offset(-1, 0).Value, _ vbTextCompare) < 0 Then HTH, Wouter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i keep my formatting when linking from one sheet to another | Excel Worksheet Functions | |||
Is there a shortcut for applying sheet formatting to another sheet | Excel Discussion (Misc queries) | |||
Copy Excel Sheet to another sheet and preserve formatting? | Excel Programming | |||
Help with formatting Excel sheet. | Excel Programming | |||
Sheet Formatting | Excel Programming |