Can someone help with these For loops and nested IF statements?

[psulion01]

I think my syntax is just wrong somewhere but maybe I'm just missing an easier solution. Here are the blocks of code... I don't think the sheet will help so much but I can post if necessary.

Basically the first piece of code is looping through columns checking to see if the column total (TotalAllocated) matches the target total (TotalSold). If it doesn't match (TotalAllocated should always be<= TotalSold), then 1 is added to the total and the code steps over to the next column. If I run out of columns then it loops back to the first column with data (c=3). This seems to be working just fine but I want to make sure my For and If statements are done correctly. I get the feeling I should be including End If statements but when I add them I start getting Block errors. Is it ok to leave the code as is, without the End If? I understand that some single-line If statements are ok to use, while others are not, but I've yet to find a clear explanation of these "rules".

'Loop to check that TotalAllocated = TotalSold
'If TotalAllocated< TotalSold, 1 share will be added, one client at a time from largest order to smallest
'until TotalAllocated = TotalSold
  
    For c = 3 To LastCol
        If TotalAllocated = TotalSold Then Exit For
            Cells(4, c).Value = Cells(4, c).Value + 1
            TotalAllocated = Application.Sum(Range(Cells(4, 3), Cells(4, LastCol)))
            Cells(4, LastCol + 1).Value = TotalAllocated
            If c = LastCol Then c = 2
    Next c

The next piece of code is somewhat similar...I need to loop through all rows, checking to see if the row total (RowAllocated) matches the target (RowSold). If the row values match then I need to proceed to the next row. If they don't match then I need to check if the column totals have not yet maxed out, and if so, add 1 to the current row,column. If the column is maxed out then I need to step through to the next column of the row until i find one that can take the +1. This loop continues through each row until all rows are full. My code below seems to get stuck in an infinite loop once I hit the last row (it just keeps looping from the first to the last column).

The sheet below shows the code about halfway through, so you can see some rows/columns completed with others still to go.

Can anyone offer some help? Much appreciated in advance...

    For r = 7 To LastRow
        RowSold = Cells(r, 1)
        RowAllocated = Cells(r, LastCol + 1)
                
        If RowSold > RowAllocated Then
       
        For c = 3 To LastCol
            If Cells(LastRow + 1, c)< Cells(4, c) Then
                If RowSold = RowAllocated Then Exit For
                    Cells(r, c).Value = Cells(r, c).Value + 1
                    RowAllocated = Application.Sum(Range(Cells(r, 3), Cells(r, LastCol)))
                    Cells(r, LastCol + 1).Value = RowAllocated
                    Cells(LastRow + 1, c).Value = Application.Sum(Range(Cells(7, c), Cells(LastRow, c)))
                    If c = LastCol Then c = 2
                    
                Else
                    If c = LastCol Then c = 2
                
            End If
        Next c
    
    Next r

Allocation Sheet - Loop Version.xls
	A	B	C	D	E	F
1		ClientName	Purple	Grey	White	Totals
2		OrderQty	10,288	9,571	5,508	25,367
3		%ofTotalOrder	40.5566%	37.7301%	21.7132%	100.0000%
4		SharestobeAllocated	1,096	1,019	586	2,701
5
6	Quantity	Price
7	300	25.89	122	113	65	300
8	300	25.895	122	113	65	300
9	100	26.365	41	38	21	100
10	200	26.38	82	75	43	200
11	200	26.39	82	75	43	200
12	500	26.4	202	189	108	499
13	200	26.41	81	75	43	199
14	200	26.42	81	75	43	199
15	200	26.46	81	75	43	199
16	100	26.48	40	37	21	98
17	200	26.55	81	75	43	199
18	80	26.56	32	30	17	79
19	121	26.57	49	45	26	120
20	TotalSold	2,701	1,096	1,015	581	2,685
21	AveragePrice	26.3168	26.3170	26.3163	26.3159	26.3165
Sheet1

 

[AlphaFrog]

Is it ok to leave the code as is, without the End If? I understand that some single-line If statements are ok to use, while others are not, but I've yet to find a clear explanation of these "rules".

Yes it's OK to use single-line If statements without End If. That, in fact, is the "rule". If your If statement is on one line (the part after Then is on the same line), you don't need the block End If.

If you highlight the If in your vba code and press F1, you will get this syntax help on If...Then...Else

Excel VBA Help: If...Then...Else Statement

Syntax

If condition Then [statements] [Else elsestatements]

Or, you can use the block form syntax:

If condition Then
[statements]

[ElseIf condition-n Then
[elseifstatements] ...

[Else
[elsestatements]]

End If

Updated code...

    For r = 7 To LastRow
    
        RowSold = Cells(r, 1)
        RowAllocated = Cells(r, LastCol + 1)

        c = 3
        Do While RowSold > RowAllocated
                 
            If Cells(LastRow + 1, c) < Cells(4, c) Then
                Cells(r, c).Value = Cells(r, c).Value + 1
                RowAllocated = RowAllocated + 1
                Cells(r, LastCol + 1).Value = RowAllocated
                Cells(LastRow + 1, c).Value = Cells(LastRow + 1, c).Value + 1
            End If
            
            c = c + 1
            If c > LastCol Then c = 3
            
        Loop
    Next r

 

[psulion01]

Yes it's OK to use single-line If statements without End If. That, in fact, is the "rule". If your If statement is on one line (the part after Then is on the same line), you don't need the block End If.

If you highlight the If in your vba code and press F1, you will get this syntax help on If...Then...Else

got it... thanks. So when writing nested if statements should one always use ElseIf, and can you stack multiple ElseIfs in one If statement like If... Then... elseif...then... elseif... then... Else?

Updated code...

    For r = 7 To LastRow
    
        RowSold = Cells(r, 1)
        RowAllocated = Cells(r, LastCol + 1)

        c = 3
        Do While RowSold > RowAllocated
                 
            If Cells(LastRow + 1, c) < Cells(4, c) Then
                Cells(r, c).Value = Cells(r, c).Value + 1
                RowAllocated = RowAllocated + 1
                Cells(r, LastCol + 1).Value = RowAllocated
                Cells(LastRow + 1, c).Value = Cells(LastRow + 1, c).Value + 1
            End If
            
            c = c + 1
            If c > LastCol Then c = 3
            
        Loop
    Next r

this looks great... very clean. Can you explain what i was doing wrong? I know my code was a bit messy but was the main flaw my nesting of For loops (is that usually a no-no?) Or was it that a do loop is just the better/correct way to get this to work? Also, do you know of a good, detailed guide that explains when best to use Do loop, For loop, etc. Ive read stuff here and on ozgrid but curious if other helpful resources exist.

Thanks again... good stuff.

Best,
mike

 

[AlphaFrog]

got it... thanks. So when writing nested if statements should one always use ElseIf, and can you stack multiple ElseIfs in one If statement like If... Then... elseif...then... elseif... then... Else?

Almost correct. Yes you can stack multiple ElseIfs in one statement, but using ElseIf is not the same as nested Ifs. They are different.

When using miltiple ElseIfs, the first If or ElseIF that is True is the one that is evaluated.

When using nested Ifs, the outer If has to be true before the inner Ifs are evaluated.

Similar but not the same.

Can you explain what i was doing wrong? (in the original macro code)

Remove this from your original code. It should work then.

                    Else
                        If c = LastCol Then c = 2

do you know of a good, detailed guide that explains when best to use Do loop, For loop, etc.

There are plenty of good resources but I don't have a specific recommendation.

Generally speaking...

I use a Do\Loop when i want to loop an unknown number of times but want to loop until a condition is met. In your case, Loop while RowSold > RowAllocated which is an unknown number for each row.

Use a For\Next loop when you want to loop a known number of loops.

 

[psulion01]

Almost correct. Yes you can stack multiple ElseIfs in one statement, but using ElseIf is not the same as nested Ifs. They are different.

When using miltiple ElseIfs, the first If or ElseIF that is True is the one that is evaluated.

When using nested Ifs, the outer If has to be true before the inner Ifs are evaluated.

Similar but not the same.

Hmm... Ok so is this:

If Cells(1, LastCol + 1) = "Totals" Then
        Columns(LastCol + 1).ClearContents
        ElseIf Cells(1, LastCol) = "Totals" Then
            Columns(LastCol).ClearContents
End If

essentially the same as this:

If Cells(1, LastCol + 1) = "Totals" Then
        Columns(LastCol + 1).ClearContents
        Else
            If Cells(1, LastCol) = "Totals" Then
                Columns(LastCol).ClearContents
            End If
End If

If so, then the second piece of code would really be considered an ElseIf, and NOT a nested IF, correct? Nested IFs would look something like this?

If Cells(1, LastCol + 1) = "Totals" Then
        Columns(LastCol + 1).ClearContents
            If Cells(1, LastCol) = "Totals" Then
                Columns(LastCol).ClearContents
            End If
End If

I think I'm getting this now... Thanks. As for the other stuff, I'll give it a look. Unfortunately, now my code is working great but the logic is flawed, so the end result isn't correct. In case you're wondering, the loop is attempting to allocate shares pro-rata from the pool (cols A and B) to the clients (Purple, Grey, White). The rounddown is needed because I must allocate whole shares (I can't use fractionals), but in the end every client needs to have the same average price that matches the master pool's average price (B21 on my sheet). If you care to take a stab at it I'll gladly post all of my code...

Thx!