Nested IF statements VBA

Question

I have the below code which works fine for importing data from one sheet to another via a simple loop code.

            Dim y As Integer
            y = 15
            For X = 15 To LR Step 1
            If Len(WS.Range("B" & X)) >= 7 Then
                WS.Range("B" & X).Copy
                WS1.Range("E" & y).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            y = y + 1
            Else
            End If
            Next X

I would like to add an IF statement that if the LEN is greater than 7 sum all the above values up until the last time a value was greater than 7 len and group the values so they can be collapsed into a subtotal.

I imagine this would be a IF statement nested into my current code but unsure of how I would reference the points.

Added pictures to help explain what I currently have and what I would like to achieve with your help.

Why don't you just make a counter variable that stores the values as you are looping through them, then when you reach the place with the length greater than 7 just print the value of the counter where you want it and clear it out so it can be used again. Also, I do not fully understand your code. What does x & y stand for? your pictures start at row 3, but your variables at 15. I am having a difficult time following your process. — PartyHatPanda, Oct 24 '16 at 13:44
X & Y are the variables being used for loops. Agreed this does not match up with the pictures in the table as it was just a draft of the end result. Thanks I will look into counter variables I have never used them before so hoping I can pick this up quite easily. :) Thanks for your suggestion — Sean Bailey, Oct 24 '16 at 13:53
Don't be afraid of the term counter variable. All it means is a variable that holds and adds data as a loop goes. think of `total = total + newNumber`, where total just keeps adding new numbers to itself until it needs to be used. — PartyHatPanda, Oct 24 '16 at 13:55
Actually this may be answered by http://stackoverflow.com/questions/11707888/sum-function-in-vba — Skip Intro, Oct 24 '16 at 14:03

score 0 · Answer 1 · answered Oct 24 '16 at 14:52

a proposal

Dim y As Integer
y = 15
Total = 0
For x = 15 To LR Step 1
    If Len(ws.Range("B" & x)) > 7 Then
        ws1.Range("E" & y) = Total
        Total = 0
        y = y + 1
    ElseIf Len(ws.Range("B" & x)) = 7 Then
        ws.Range("B" & x).Copy ws1.Range("E" & y)
        Total = Total + ws.Range("B" & x)
        y = y + 1
    End If
Next x

score 0 · Accepted Answer · answered Oct 24 '16 at 15:24

Try this approach: First declare an Integer type variable

Dim iSR As Integer: iSR = 15

Note that I have assigned iSR a value of 15. As we will use this variable to hold the Starting Row number, ensure that you assign it the row number where the numbers start. Now, where your If Len(WS.Range("B" & X)) >= 7 Then condition ends and before Next X, add the following code (so that its still within your FOR loop)

If Len(WS.Range("B" & x).Value) > 7 Then
    oWS.Range("C" & x).Formula = "=Sum(C" & iSR & ":C" & oWS.Range("C" & x).Offset(-1).Row & ")"
    iSR = oWS.Range("C" & x).Offset(1).Row
End If

To quickly explain the code: IF condition checks if the length of text is greater than 7. If it is, it assigns a SUM formula in "C" column of the same row where the text is. Formula uses iSR as your starting row and by using Offset, gets the row number of the row prior to the current row. Next line simply gets the row number of the row after the current row so that we know what our next iSR number is

Thanks very much for this, it was of great help!! :) – Sean Bailey Oct 31 '16 at 13:52 — Sean Bailey, Oct 31 '16 at 13:52

Nested IF statements VBA

2 Answers2