CODEWITHSUNDEEP
vbaexcel
jmaz
jmaz

Reputation: 527

Why Does This For Loop Not Execute?

This non-looping code executes with no problem:

If InStr(1, Sheets(1).Range("A1"), "blah") > 0 Then
    Sheets(2).Range("A1") = Sheets(1).Range("B1")
End If

But I need to iterate through several rows; hence, a loop:

Dim i As Integer
For i = 1 To 10
    If InStr(1, Sheets(1).Cells(i, 1), "blah") > 0 Then
        Sheets(2).Cells(i, 1) = Sheets(1).Cells(i, 2)
    Else Sheets(2).Cells(i, 1) = ""
    End If
Next

The loop compiles and does not crash, but fails to return any output. Why?

Upvotes: 0

Views: 111

Answers (3)

David Zemens
David Zemens

Reputation: 53663

If you teach a man to fish... here is not an answer, but how you can figure out the problem on your own:

Add a breakpoint in your code by putting the cursor on the line below, and pressing F9.

Sheets(2).Cells(i, 1) = Sheets(1).Cells(i, 2)

If you did it correctly, then you will see it like this in the VBE:

enter image description here

Press F5 to run your code.

The VBE will highlight this line yellow when/if it hits the line. This essentially pauses code execution so you can examine & trace what's happening. Here is when you can enter Debug.Print statements (or MsgBox as per my example and SOrceri's example, above) or you can use the Immediates window, or the Locals window to examine variables & their values (this is more advanced).

If "nothing" happens (i.e., the line is never highlighted yellow) there are only two things that I can think of.

  1. Sheets(1).Cells(i,2) is empty, or
  2. The condition in your boolean expression is never satisfied.

The breakpoint will allow you to debug and figure out which of those (or potential other conditions that I haven't thought of) might be contributing to the apparent "error".

Update I think the problem really is your logic, unfortunately. I will try to provide an example that can be as explicit as possible.

Sub Test()
Dim i As Integer
Dim sht1Cell As Range
Dim sht2Cell As Range
For i = 1 To 10
    Set sht1Cell = Sheets(1).Cells(i, 1)
    Set sht2Cell = Sheets(2).Cells(i, 1)

    'Now, test whether "blah" is found in sht1Cell:
    If InStr(1, sht1Cell.Value, "blah") > 0 Then
        'If it contains "blah", then put this value in sheet 2.
        'Note: you were previously putting values from Column B.
        '      but this is taking the exact cell value from 
        '      column A, so I KNOW this value cannot be a blank value.
        sht2Cell.Value = sht1Cell.Value
    Else:
        'Does not contain "blah", so make sheet 2 have a blank
        'This blanks out any value that might have previously been
        'present in sh2Cell.
        sht2Cell.Value = ""
    End If
Next
End Sub

If this is not achieving the desired results, I am 100% certain the problem is in your logic, which does not adequately describe the condition(s) and result(s) you hope to achieve. Wherein it differs, is anyone's guess, unless you can more thoroughly describe what you're trying to do.

Upvotes: 1

Sorceri
Sorceri

Reputation: 8043

This should work. Also noticed that you are referencing the cell and not the value held in the cell

***Note this is a crap way to debug but...what does the msgbox prompt say? It should say 0 or 1 and the second msgbox should tell you what is going to be put into the second sheet. AGAIN this is a total crap way to debug

Sub Test()
Dim iVal As Integer
    For i = 1 To 10
        iVal = InStr(1, Sheets(1).Cells(i, 2), "blah")
        MsgBox CStr(iVal)
        If iVal > 0 Then
        'go to second sheet column 1 and enter in the value thats in the 2nd column on sheet 1
        MsgBox "Adding to Sheet 2: " & Sheets(1).Cells(i, 2).Value
            Sheets(2).Cells(i, 1).Value = Sheets(1).Cells(i, 2).Value
        End If
    Next
End Sub

Upvotes: 2

wesmantooth
wesmantooth

Reputation: 625

Add a 1 and a 0 to your instr formula 

Instr(1, Sheets(1).Cells(i,1), "blah") > 0

Also, are you sure that Sheets(2)'s values are the ones to be changed?

Upvotes: 2

Related Questions