Loop Help

[wisewood]

Could someone please help me with the loop aspect of this code; I've got this far, but it keeps listing everything, and i only want the ones where the If clause applies.

The loop lists a number of 4 digit reference numbers. I only want it to list ones where the reference number (Gx) is greater than the last number displayed on Sheet2 (Jx).

It's driving me insane - if i can get this part working, it will put me a huge step closer to finishing the project - and scoring huge kudos with the boss.

Sub list()
Fx = Dir("g:\marketing\invoices\" & "MISC*.xls")
Hx = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Jx = Sheets("Sheet2").Cells(Hx, 1)

Cells(1, 1) = Jx
Cells(2, 1).Select

    Do While Len(Fx) > 0
    Gx = Mid(Fx, 5, 4)
        If Gx > Jx Then
            ActiveCell.Formula = Gx
            ActiveCell.Offset(1, 0).Select
            Fx = Dir()
        End If
    Loop

End Sub

 

[Andrew Poulsom]

Try:

Sub list()
    Fx = Dir("g:\marketing\invoices\" & "MISC*.xls")
    Hx = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Jx = Sheets("Sheet2").Cells(Hx, 1)
    Cells(1, 1) = Jx
    Cells(2, 1).Select
    Do While Len(fx) > 0
        Gx = CInt(Mid(fx, 5, 4))
        If Gx > Jx Then
            ActiveCell.Formula = Gx
            ActiveCell.Offset(1, 0).Select
        End If
        fx = Dir()
    Loop
End Sub

In your code Gx was a string not a number. I also moved the Dir outside the If clause.

 

[wisewood]

Thanks Andrew.

I'm a rather shoddy PHP man usually - VBA is much more strict.