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
– PartyHatPanda, Commented Oct 24, 2016 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
– Sean Bailey, Commented Oct 24, 2016 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
– PartyHatPanda, Commented Oct 24, 2016 at 13:55
Actually this may be answered by stackoverflow.com/questions/11707888/sum-function-in-vba — Skip Intro
– Skip Intro, Commented Oct 24, 2016 at 14:03

Zac · Accepted Answer · 2016-10-24 15:24:51Z

0

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

answered Oct 24, 2016 at 15:24

Zac

1,9501 gold badge10 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Bailey Over a year ago

Thanks very much for this, it was of great help!! :)

h2so4 · Accepted Answer · 2016-10-24 14:52:25Z

0

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

answered Oct 24, 2016 at 14:52

h2so4

1,5771 gold badge10 silver badges11 bronze badges

Collectives™ on Stack Overflow

Nested IF statements VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related