Nested loops??

[CG Rosén]

Good Day Group,

Struggle with below problem, grateful for some hints how start thinking.

Brgds

CG Rosén

My database looks like this:

A B C D E
Item Type SubType Size Stock
xxx Spirits Aquavit 0,75 2
zzzz Spirits Aquavit 0,50 4
yyy Spirits Brandy 1,00 1
qqq Spirits Brandy 0,70 5
www Spirits Cognac 0,5 3
etc etc

Trying to present the data in a worksheet like this:

A B C D E
1 1,00 0,75 0,70 0,50
2 Aquavit 0 2 0 4
3 Brandy 1 0 1 0
4 Cognac 0 0 0 3
etc
etc

This is start to do it by a nested loop (gives run time error 91)

SubType = Array("Aquavit", _
"Brandy", _
"Cognac", _
"Gin", _
"Liqueuer", _
"Other Spirits", _
"Rum", _
"Whisky", _
"Vodka")

For i = 0 To 8

With Worksheets(1).Range("C1:C10000")

Set k = .Find(SubType(i), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not k Is Nothing Then
firstAddress = k.Address
Do
'---------------------------------------------------------------------
With Worksheets(1).Range("D110000")

Set j = .Find("1,00", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not j Is Nothing Then
secAddress = j.Address
Do
r = k.Row

Code ??????

Set j = .FindNext(j)
Loop While Not j Is Nothing And j.Address <> secAddress

End If

End With
'---------------------------------------------------------------------------
--
Set k = .FindNext(k)
Loop While Not k Is Nothing And k.Address <> firstAddress
??????????????????????????? error 91

End If
End With
Next i

 

[Tom Ogilvy]

You can do this with a pivot table without any code.