Speed up a VLookUp loop

[wizuriel]

Hello.

I've just finished writing a formula that looks up some information for me across multiple worksheets instead of me having to manually do it. The code works perfectly, but runs super slow . It actually seems a lot faster for me just to manually write the vlookup formula and just paste it down the worksheet . I'm wondering if anyone has any tips for speeding it up? The worksheet can have something like upwards of 30k entries I need to look up.

The relevant code

            ActiveWorkbook.Sheets(Worksheet).Activate  'going to the worksheet that has the data I need
            localcol = FindLastCol(1) 'finding the last column in the worksheet
            col = FindCol(finding, localcol) 'finding the col that holds the data I need

            locallastrow = FindLastRow(Mid(Cells(1, col).Address, InStr(Cells(1, 1).Address, "$") + 1, InStr(2, Cells(1, col).Address, "$") - 2)) 'find last row of the worksheet
            myrange = Worksheet & "!" & Cells(1, 1).Address & ":" & Cells(locallastrow, col).Address  'range of the sheet to use in the vlookup

            ActiveWorkbook.Sheets("data").Activate 'worksheet that I want to show the data in

            For innercounter = 2 To max 'going through all cells in data worksheet
                Vtest = Application.VLookup(Int(test), Range(myrange), col, False) 'test is the value I'm looking up
                If (IsError(Vtest)) Then
                    Cells(innercounter, counter) = " "  
                Else
                    Cells(innercounter, counter) = Vtest
                End If
            Next innercounter