Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Super Hard Question
linked from: http://www.mrexcel.com/forum/showthread.php?t=320701
I was thinking 1 full day yesterday about this....I still couldn't think a way out of this. I need to convert database sheet to Navision template as shown below.(please refer to the above link for the spreadsheet) The Navision template is sorted by date. When that date is selected, Only the product and customer to that date is extracted....I just couldn't think of any possbilities on how this could be done. This is beyond Excel abilities, right? link: http://www.geocities.com/gjfeng/protoV2.xls |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Super Hard Question
Nothing is beyonnd VBA. try this code
Sub makeNavision() With Sheets("db") RowCount = 2 OrderDate = 0 CustomerID = "" Do While Range("A" & RowCount) < "" If OrderDate < .Range("A" & RowCount) Then OrderDate = .Range("A" & RowCount) 'make new sheet Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) newsht.Name = Format(OrderDate, "DD MMMM YYYY") With newsht .Range("A1") = "Order Date" .Range("B1") = Order .Range("A2") = "Delivery Date" .Range("A3") = "Posting Date" .Range("A4") = "Unit Price" .Range("A5") = "Item No" .Range("A6") = "Product Name" End With OrderCol = Range("C1").Column OrderRow = 7 UnitCount = 1 Else NewCustomerID = .Range("E" & RowCount) If NewCustomerID = CustomerID Then OrderCol = OrderCol + 2 UnitCount = UnitCount + 1 Else OrderCol = Range("C1").Column OrderRow = OrderRow + 1 UnitCount = 1 End If End If CustomerID = .Range("E" & RowCount) CustomerAddress = .Range("D" & RowCount) ProductID = .Range("F" & RowCount) ProductName = .Range("G" & RowCount) Quant = .Range("H" & RowCount) & " " & .Range("I" & RowCount) With newsht .Cells(4, OrderCol + 1) = UnitCount .Cells(5, OrderCol) = ProductID .Cells(6, OrderCol) = ProductName .Range("A" & OrderRow) = CustomerID .Range("B" & OrderRow) = CustomerAddress End With RowCount = RowCount + 1 Loop End With End Sub "Pukka" wrote: linked from: http://www.mrexcel.com/forum/showthread.php?t=320701 I was thinking 1 full day yesterday about this....I still couldn't think a way out of this. I need to convert database sheet to Navision template as shown below.(please refer to the above link for the spreadsheet) The Navision template is sorted by date. When that date is selected, Only the product and customer to that date is extracted....I just couldn't think of any possbilities on how this could be done. This is beyond Excel abilities, right? link: http://www.geocities.com/gjfeng/protoV2.xls |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Super Hard Question
I made some changes, try this instead
Sub makeNavision() With Sheets("db") RowCount = 2 OrderDate = 0 CustomerID = "" Do While Range("A" & RowCount) < "" If OrderDate < .Range("A" & RowCount) Then OrderDate = .Range("A" & RowCount) 'make new sheet Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) newsht.Name = Format(OrderDate, "DD MMMM YYYY") With newsht .Range("A1") = "Order Date" .Range("B1") = Order .Range("A2") = "Delivery Date" .Range("A3") = "Posting Date" .Range("A4") = "Unit Price" .Range("A5") = "Item No" .Range("A6") = "Product Name" End With OrderCol = Range("C1").Column OrderRow = 7 UnitCount = 1 Else NewCustomerID = .Range("E" & RowCount) If NewCustomerID = CustomerID Then OrderCol = OrderCol + 2 UnitCount = UnitCount + 1 Else OrderCol = OrderCol + 2 OrderRow = OrderRow + 1 UnitCount = 1 End If End If CustomerID = .Range("E" & RowCount) CustomerAddress = .Range("D" & RowCount) ProductID = .Range("F" & RowCount) ProductName = .Range("G" & RowCount) Quant = .Range("H" & RowCount) & " " & .Range("I" & RowCount) With newsht .Cells(4, OrderCol + 1) = UnitCount .Cells(5, OrderCol) = ProductID .Cells(6, OrderCol) = ProductName .Range("A" & OrderRow) = CustomerID .Range("B" & OrderRow) = CustomerAddress .Cells(OrderRow, OrderCol + 1) = Quant End With 'autofit columns newsht.Columns.AutoFit RowCount = RowCount + 1 Loop End With End Sub "Pukka" wrote: linked from: http://www.mrexcel.com/forum/showthread.php?t=320701 I was thinking 1 full day yesterday about this....I still couldn't think a way out of this. I need to convert database sheet to Navision template as shown below.(please refer to the above link for the spreadsheet) The Navision template is sorted by date. When that date is selected, Only the product and customer to that date is extracted....I just couldn't think of any possbilities on how this could be done. This is beyond Excel abilities, right? link: http://www.geocities.com/gjfeng/protoV2.xls |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Super Hard Question
I think if the sqame product ID is used by two differrent customers it should
appear in the same columns. Here is the modified code. Sub makeNavision() With Sheets("db") RowCount = 2 OrderDate = 0 CustomerID = "" Do While Range("A" & RowCount) < "" If OrderDate < .Range("A" & RowCount) Then OrderDate = .Range("A" & RowCount) 'make new sheet Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) newsht.Name = Format(OrderDate, "DD MMMM YYYY") With newsht .Range("A1") = "Order Date" .Range("B1") = Order .Range("A2") = "Delivery Date" .Range("A3") = "Posting Date" .Range("A4") = "Unit Price" .Range("A5") = "Item No" .Range("A6") = "Product Name" End With OrderCol = Range("C1").Column LastOrderCol = OrderCol OrderRow = 7 UnitCount = 0 Else NewCustomerID = .Range("E" & RowCount) If NewCustomerID < CustomerID Then OrderRow = OrderRow + 1 End If End If ProductID = .Range("F" & RowCount) CustomerID = .Range("E" & RowCount) CustomerAddress = .Range("D" & RowCount) ProductName = .Range("G" & RowCount) Quant = .Range("H" & RowCount) & " " & .Range("I" & RowCount) With newsht 'check if ProductID already exists on sheet Set c = .Rows(5).Find(what:=ProductID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then OrderCol = LastOrderCol LastOrderCol = LastOrderCol + 2 UnitCount = UnitCount + 1 .Cells(4, OrderCol + 1) = UnitCount Else OrderCol = c.Column End If .Cells(5, OrderCol) = ProductID .Cells(6, OrderCol) = ProductName .Range("A" & OrderRow) = CustomerID .Range("B" & OrderRow) = CustomerAddress .Cells(OrderRow, OrderCol + 1) = Quant End With 'autofit columns newsht.Columns.AutoFit RowCount = RowCount + 1 Loop End With End Sub "Pukka" wrote: linked from: http://www.mrexcel.com/forum/showthread.php?t=320701 I was thinking 1 full day yesterday about this....I still couldn't think a way out of this. I need to convert database sheet to Navision template as shown below.(please refer to the above link for the spreadsheet) The Navision template is sorted by date. When that date is selected, Only the product and customer to that date is extracted....I just couldn't think of any possbilities on how this could be done. This is beyond Excel abilities, right? link: http://www.geocities.com/gjfeng/protoV2.xls |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Super easy question | Excel Worksheet Functions | |||
Fx If.....Question from Super Newbie... | Excel Discussion (Misc queries) | |||
simple but hard question | Excel Discussion (Misc queries) | |||
simple but very hard question | Excel Discussion (Misc queries) | |||
Super Easy Question -- " | Excel Programming |