Nested Loop
 

Hi
just as a question: Why not use the feature 'Data - Subtotals'

--
Regards
Frank Kabel
Frankfurt, Germany


Federico Ginnari wrote:
Hi, i'm stuck in a little project that i'm working on, the problem is
that i can't get my nested loop to work as i wanted, here is an
example of the information on the sheet, and below is an example of
my code.

Title
$$$$
$$$$
$$$$

$$$$
TOTAL
Title
$$$$
$$$$
$$$$

$$$$
TOTAL

lngTotalRows = Sheet1.UsedRange.Rows.Count
Dim L as long
Dim Lo As Long
L = 12
Lo = 16
For L = L To lngTotalRows
If Cells(L, 1).Text < "" Then
For Lo = Lo To lngTotalRows
If Cells(Lo, 1).Text < "" Then
Cells((Lo - 1), 8) =
WorksheetFunction.Sum(Range(Cells(L, 8), Cells(Lo, 8)))
End If
Next Lo
End If
Next L

the idea for this loop is to give me the total for each title, the
problem with this code is that the range is not being updated
correctly, the first position for the range always stays on the same
place, also because on the way that the loop works, i'm not getting a
total for the last title, how can i overcome this problem?, or is
there an easier way to do this? what i'm missing? help!!, if anyone
needs more information please let me know, thanks in advance

Nested Loop
 

Hi
just see Excel's help or try it out :-)
- select your column
- goto this menu feature and activate it
- follow the dialog

--
Regards
Frank Kabel
Frankfurt, Germany


Federico Ginnari wrote:
Frank, thanks for your response, but i'm not familiar with this
feature, can anyone show me an example of this? thank you.

"Frank Kabel" wrote:

Hi
just as a question: Why not use the feature 'Data - Subtotals'

--
Regards
Frank Kabel
Frankfurt, Germany


Federico Ginnari wrote:
Hi, i'm stuck in a little project that i'm working on, the problem
is that i can't get my nested loop to work as i wanted, here is an
example of the information on the sheet, and below is an example of
my code.

Title
$$$$
$$$$
$$$$

$$$$
TOTAL
Title
$$$$
$$$$
$$$$

$$$$
TOTAL

lngTotalRows = Sheet1.UsedRange.Rows.Count
Dim L as long
Dim Lo As Long
L = 12
Lo = 16
For L = L To lngTotalRows
If Cells(L, 1).Text < "" Then
For Lo = Lo To lngTotalRows
If Cells(Lo, 1).Text < "" Then
Cells((Lo - 1), 8) =
WorksheetFunction.Sum(Range(Cells(L, 8), Cells(Lo, 8)))
End If
Next Lo
End If
Next L

the idea for this loop is to give me the total for each title, the
problem with this code is that the range is not being updated
correctly, the first position for the range always stays on the
same
place, also because on the way that the loop works, i'm not getting
a total for the last title, how can i overcome this problem?, or is
there an easier way to do this? what i'm missing? help!!, if anyone
needs more information please let me know, thanks in advance

Nested Loop
 

i checked it out, but i still have no idea on how to program this into the
macro =(

Nested Loop
 

The easiest way to do it is to record a macro when you do it manually.

Start by selecting your range, then data|subtotals.

After you get your code, you may want to generalize it. Post back with your
questions and the parts of the code that need help.



Federico Ginnari wrote:

i checked it out, but i still have no idea on how to program this into the
macro =(

--

Dave Peterson

Nested Loop
 

Dave, thanks for your help too, i've been trying to get this data - subtotal
to work with my project but i don't think it's the way to go, all this extra
information that this function adds messes up the rest of the information in
the sheet, and to be hones, it just gives you to much sub totals!. is there
another way to do this??? the big problem that i'm having on this proect is
that the data is not always the same size, i mean the titles even thought
they are on the same column, but they are not always on the same row, and
some titles have more items that others. on my 1st post i have an example of
the code, any other comments are apriciated, thanks.

Nested Loop
 

If you could put the indicator on each line, then it sounds like a pivottable
would work nicely.

To add the Title to each subsequent row, look at Debra Dalgleish's site:
http://www.contextures.com/xlDataEntry02.html

Then you could use a dynamic range name to make the pivottable will reflect the
current data--again from Deb's site:

http://www.contextures.com/xlNames01.html#Dynamic

And finally, some links for learning about pivottables.

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/...ivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


======
I find that if I lay out my data nicely, then I can use the features/functions
that are built into excel. It really makes life easier in the long run.



Federico Ginnari wrote:

Dave, thanks for your help too, i've been trying to get this data - subtotal
to work with my project but i don't think it's the way to go, all this extra
information that this function adds messes up the rest of the information in
the sheet, and to be hones, it just gives you to much sub totals!. is there
another way to do this??? the big problem that i'm having on this proect is
that the data is not always the same size, i mean the titles even thought
they are on the same column, but they are not always on the same row, and
some titles have more items that others. on my 1st post i have an example of
the code, any other comments are apriciated, thanks.

--

Dave Peterson

Nested Loop
 

Thanks Dave, i'll look into this articles

"Dave Peterson" wrote:

If you could put the indicator on each line, then it sounds like a pivottable
would work nicely.

To add the Title to each subsequent row, look at Debra Dalgleish's site:
http://www.contextures.com/xlDataEntry02.html

Then you could use a dynamic range name to make the pivottable will reflect the
current data--again from Deb's site:

http://www.contextures.com/xlNames01.html#Dynamic

And finally, some links for learning about pivottables.

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/...ivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


======
I find that if I lay out my data nicely, then I can use the features/functions
that are built into excel. It really makes life easier in the long run.



Federico Ginnari wrote:

Dave, thanks for your help too, i've been trying to get this data - subtotal
to work with my project but i don't think it's the way to go, all this extra
information that this function adds messes up the rest of the information in
the sheet, and to be hones, it just gives you to much sub totals!. is there
another way to do this??? the big problem that i'm having on this proect is
that the data is not always the same size, i mean the titles even thought
they are on the same column, but they are not always on the same row, and
some titles have more items that others. on my 1st post i have an example of
the code, any other comments are apriciated, thanks.

--

Dave Peterson