Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested loops??
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("D1:D10000") 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested loops??
You can do this with a pivot table without any code.
-- Regards, Tom Ogilvy "CG Rosén" wrote in message ... 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("D1:D10000") 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with nested for loops | Excel Discussion (Misc queries) | |||
Help with nested for loops | Excel Worksheet Functions | |||
Max limit of 7 nested loops | Excel Worksheet Functions | |||
RANGE & NESTED LOOPS | Excel Programming | |||
LOOPS | Excel Programming |