End If/Next C Statement with For Loop / VBA

Question

I have a fairly complex for For Loop with an If Statement inside and another For Loop inside that. And given a certain criteria (Namely If InStr(1, q.Value, "Total")) inside that second For Loops I would like to just end the entire If Statement and move to the Next C.

I understand that this is layered like an onion and there might not be an easy way out.

For Each C In copyRng

        If IsNumeric(C) And C.Value <> "0" And Len(C) <> 0 And C.Value <> "2017" Then 

            Set rowRange = xSheet.Range(C, C.EntireColumn.Cells(1)) 'set range from cell up to the top cell of the comment/ Fix the 2017 thing

            For Each q In rowRange 'Loop through that range and find the Account number just above it and set it as rowSrc
                If InStr(1, q.Value, "C-") And Not ISIN(C, uniqueVal) Then Set rowSrc = q
                If InStr(1, q.Value, "Total") Then End If 'At this point I want to leave the entire If Statement and move on to the next C
            Next q


            Set colSrc = C.EntireRow.Offset(0).Cells(1) 'find alert connected with the number
            numCol = DestSh.Cells.Find(colSrc.Value, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column 'look for the column in which the same alert is listed
            numRow = DestSh.Cells.Find(rowSrc.Value, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row 'look for row in which the same account is listed

            'Set destination
            Set destRng = DestSh.Cells(numRow, numCol)

            'Copy to destination Range
            C.Copy destRng

        End If

    Next C

`If` statements dont work like that. Your line `If..Then End If` is really just saying `If...Then : Do Nothing : End If` since `End If` is just the final line on an `If` block. Likewise, your lines `If...Then _` are problematic since those are line continuation tokens. Don't use them like that. It makes your code difficult to read and to debug. The only way to continue to the next C would be to wrap the code you dont want to run in the reverse of the if `If Not Instr() Then` and then, since the condition wont satisfy, the next C will run. — Brandon Barney, Aug 28 '17 at 12:30
@BrandonBarney thanks for the help. I really wasn't sure how the If statements worked and it makes a lot more sense now. — ShieldData, Aug 28 '17 at 12:50

trincot · Accepted Answer · 2017-08-28T12:58:59.927

1

You need Exit For and then after the loop, put the remaining code in yet another If block:

For Each C In copyRng
    If IsNumeric(C) And C.Value <> "0" And Len(C) <> 0 And C.Value <> "2017" Then
        Set rowRange = xSheet.Range(C, C.EntireColumn.Cells(1))

        For Each q In rowRange
            If InStr(1, q.Value, "C-") And Not ISIN(C, uniqueVal) Then Set rowSrc = q
            If InStr(1, q.Value, "Total") Then Exit For ' Exit loop prematurely
        Next q
        If q is Nothing Then ' Skip if loop was exited prematurely
            Set colSrc = C.EntireRow.Offset(0).Cells(1)
            numCol = DestSh.Cells.Find(colSrc.Value, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column
            numRow = DestSh.Cells.Find(rowSrc.Value, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row

            Set destRng = DestSh.Cells(numRow, numCol)
            C.Copy destRng
        End If
    End If
Next C

edited Aug 28 '17 at 12:58

answered Aug 28 '17 at 12:35

trincot

317,000
35
244
286

Thanks for the help. I am realizing that If statements work different from what I imagined. I decided to try out your code and I received the error message "Object variable or With-Variable not identified". Is there a quick fix to that or could that be a larger issue the rest of my code. Should the 'Next q' line maybe be moved down? – ShieldData Aug 28 '17 at 12:50
Where did you define `q` and on which line are you geting the error? NB: No, the `Next q` should not be moved down. – trincot Aug 28 '17 at 12:51
I am getting the error on this line 'If InStr(1, q.Value, "Total") = 0 Then ' Skip if loop was exited prematurely' and I defined the q at the beginning of the code As Range. – ShieldData Aug 28 '17 at 12:53
Ok, could you replace it with `If q Is Nothing Then`? – trincot Aug 28 '17 at 12:59
I think this would be counter productive, since I want the code to Set destRng and copy C into it if 'InStr(1, q.Value, "C-") And Not ISIN(C, uniqueVal) ' and not if q is nothing. Does that make sense? – ShieldData Aug 28 '17 at 13:05
Well, the error you got indicated that `q` was `Nothing`, which is what happens when the loop completes normally. The `If` makes sure that you only execute the rest of the code if indeed the loop was completed normally (without `Exit For`). – trincot Aug 28 '17 at 13:10
Interesting! I will give this a try immediately – ShieldData Aug 28 '17 at 13:14
It did run! Unfortunately my problems aren't all solved since there are a lot of inconsistencies arising, but at least I made a step forward. Thanks a lot! – ShieldData Aug 28 '17 at 13:20

Vityata · Answer 2 · 2017-08-28T12:35:08.580

0

Remove each underscore after Then and rewrite the whole code. The logic is changed. Pretty much you do not go to the else the way you think it.

Check this: VBA - How the colon `:` works in VBA code with condition

In general, your code does not work the way you think it works. Check the following:

Public Sub TestMe()

    If 1 = 2 Then _
        Debug.Print "I am true - 1=2"
        Debug.Print "I should be also true"

    If 2 = 3 Then _
        Debug.Print "I am true 2=3"

End Sub

edited Aug 28 '17 at 12:35

answered Aug 28 '17 at 12:29

Vityata

42,633
8
55
100

1

Thanks a lot. I need to stop making such liberal use of 'Then _' to ensure cleaner code. – ShieldData Aug 28 '17 at 12:51
@FSchildorfer - as a 2. step try putting `Option Explicit` on the top of your code. Something like `If InStr(1, q.Value, "Total") Then End If ` should not be allowed to compile. – Vityata Aug 28 '17 at 12:52
I definitely will. – ShieldData Aug 28 '17 at 12:54

End If/Next C Statement with For Loop / VBA

2 Answers2