excelvbafor-loopvariables

Variable get value only after the second run of the macro


My problem is that variable1 does not get the "1" value. I have already checked several times that the investigated cells contain the "1111' value. If I run the macro 2nd time, the variable1 get the "1" value, but first time never. The correct workbook and sheet is activated (already checked). Variable1 is integer and public. The code is already tried in module and workbook too.

What can be the problem? Thanks is advance!

variable1 = 0

Workbooks.Open "workbook1.xlsx"

Workbooks("workbook1.xlsx").Activate

Workbooks("workbook1.xlsx").Worksheets("sheet1").Activate

For i = 1 To 300
    If Cells(i, 2) = "1111" Then
    variable1 = 1
    Else
    End If
Next i

If variable1 = 1 Then
    GoTo xxx
Else
End If

Also tried in the for loop the following, but the result is the same:

Workbooks("workbook1.xlsx").Worksheets("sheet1").Cells(i, 2)

If I open the "workbook1.xlsx" manually, than I run the macro, the result is OK.

I have also tried a 5 sec waiting time after opening the workbook, but the result is NOK.


Solution

  • This is what I used, cleaned up your code a bit but kept most like you had it so it's easily understandable where the error may lie:

    Option Explicit
    Public variable1 As Integer
    Sub test()
        variable1 = 0
        Dim wb As Workbook
        Set wb = Workbooks.Open(Filename:="Q:\Test\TestFile.xlsx") 'change this to your actual path
        Dim i As Long
        With wb.Worksheets("Sheet1") 'change as needed
            For i = 1 To 300
                'Debug.Print .Cells(i, 2).Value 'for checking the values while testing
                If .Cells(i, 2).Value = "1111" Then
                    variable1 = 1
                End If
            Next i
        End With
            
        If variable1 = 1 Then
            GoTo xxx
        End If
        Debug.Print "Alas, no success :c"
        Exit Sub
    xxx:
        Debug.Print "Success!"
    End Sub
    

    I had hidden 2 1111 as numbers in the the B-column and one "1111" (text) further down and it went straight to xxx when it found the text "1111". Hope this helps you see where you went wrong but do let me know if this doesn't work for you either.