Super Hard Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pukka - 21 May 2008 03:53 GMT

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 to this Message

Joel - 21 May 2008 12:20 GMT

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

> linked from: http://www.mrexcel.com/forum/showthread.php?t=320701
>
[quoted text clipped - 7 lines]
>
> link: http://www.geocities.com/gjfeng/protoV2.xls

Reply to this Message

Joel - 21 May 2008 12:44 GMT

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

> linked from: http://www.mrexcel.com/forum/showthread.php?t=320701
>
[quoted text clipped - 7 lines]
>
> link: http://www.geocities.com/gjfeng/protoV2.xls

Reply to this Message

Joel - 21 May 2008 15:05 GMT

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

> linked from: http://www.mrexcel.com/forum/showthread.php?t=320701
>
[quoted text clipped - 7 lines]
>
> link: http://www.geocities.com/gjfeng/protoV2.xls

Reply to this Message