VBA trigger enter or update lookup values

Question

I have the following code:

Sub PrintToCSV()
    Dim i As Long, e As Long
    i = Worksheets("STATEMENT (2)").Range("$G$6").Value
    e = Worksheets("STATEMENT (2)").Range("$G$7").Value

    Do While i <= e
        Range("K6") = i
        Application.Wait (Now + #12:00:01 AM#)

        If Range("$X$10").Value > 0 Then
            Cells(1, 1).Value = i
        End If

        i = i + 1
    Loop
End Sub

It loops and changes value of Range("K6") as expected. However, the value of Range("K6") updates other cells values (vlookup) when I do it manually, but not with this code. How can I ensure the values of other cells depended on Range("K6") changes with this code?

Answer 1

Just FYI - do not declare like this:

Dim i, e as Long

because for this declaration only "e" is declared as long and "i" as a variant. This may cause problems somewhere later.

The correct way is:

Dim i as Long
Dim e as Long