Vlookup VBA Alternative

[helpexcel]

Is there a quicker way to use vlookup with VBA. I have a lot of data that I would just use vlookup with, across multiple sheets. Hoping there's a way to speed things up. THANKS!

 

[offthelip]

The "In" on my naming convention just signifies "input" array. I often have another array named outarr, which is the output array. As you have noticed I don't declare variables unless absolutely necessary. Declaring inarr as variant is totally unnecessary because the next line where it is loaded from the range in the worksheet means it must be a variant array, and the range determines the dimensions. So to my thinking the declaration line is a waste of space. I see lots of code on this forum with longs lists of variable declaration at the start and then just a few lines of code. I feel the declarations make the code more difficult to read. It is so easy to skip over an important line thinking it is just one of the declarations. I feel that the way you have declared the variables in fixer is exactly a case in point, the declaration statement makes it much harder to see what each variable is loading!!
I think this is much easier to read and certainly means a lot less typing (the keyboard typing), Notice that the variable names are nicely placed starting on the left , rather than being buried in two places within a statement

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Run = Range(Cells(1, 59), Cells(Lastrow, 59))
ProjectID = Range(Cells(1, 6), Cells(Lastrow, 6))
TaskID = Range(Cells(1, 1), Cells(Lastrow, 1))
StartDate = Range(Cells(1, 36), Cells(Lastrow, 36))
EndDate = Range(Cells(1, 37), Cells(Lastrow, 37))

In answer to your query yes there is a very easy way to compare times for doing things this code shows you how:

Sub timertest2()
Dim StartTime As Double
Dim Elapsed As Double
For i = 1 To 10
 For j = 1 To 6
  Cells(i, j) = 0
 Next j
Next i


StartTime = Timer
For i = 1 To 10
 For j = 1 To 6
  Cells(i, j) = Cells(i, j) + 1
 Next j
Next i


Elapsed = (Timer - StartTime)
MsgBox ("looping thru cells =" & Elapsed)


StartTime = Timer


inarr = Range(Cells(1, 1), Cells(10, 6))
For i = 1 To 10
 For j = 1 To 6
  inarr(i, j) = inarr(i, j) + 1
 Next j
Next i
Range(Cells(1, 1), Cells(10, 6)) = inarr
Elapsed = (Timer - StartTime)
MsgBox ("looping thru cells =" & Elapsed)




End Sub

Note my declaration of the doubles this is because I need to declare these as double to get the precision. So these declarations are really telling me something!!

 

[Dracius]

As you have noticed I don't declare variables unless absolutely necessary. Declaring inarr as variant is totally unnecessary because the next line where it is loaded from the range in the worksheet means it must be a variant array, and the range determines the dimensions. So to my thinking the declaration line is a waste of space.

I was getting an error and it wouldn't run until I declared it.

 

[offthelip]

Were you using option explicit?

 

[Incogn1to]

Hi, I have a similar problem as above.
I have an index match function that matches an input range with a translation table to obtain the correct fields. I wish to do this process using Array functions as the current process is to slow. I have pasted the current code below. Would this be easily translatable to an array function?

Set type = Range("type")
Set table= Range("table")
Set Source= Range("Source")
Set headers= Range("headers")
i = 2
j = 2
lMaxRow = ThisWorkbook.Sheets("TEST").Cells(Rows.Count, 1).End(xlUp).Row - 23
lMaxCol = ThisWorkbook.Sheets("translation").Cells(2, Columns.Count).End(xlToLeft).Column
    Do While i < lMaxRow
    a = ThisWorkbook.Sheets("TEST").Cells(i + 24, 12).Value
    ThisWorkbook.Sheets("output").Cells(i, 1).Value = a
        Do While j < 180
        b = Application.Index(source, i, Application.Match(Application.Index(table, Application.Match(a, type, 0), j), header, 0))
        ThisWorkbook.Sheets("output").Cells(i, j).Value = b
        j = j + 1
        Loop
    j = 2
    i = i + 1
    Loop


End Sub

 

[offthelip]

This is very suitable to do using variant arrays, which will be much faster. I can't actually write the at the moment because i don't have access to a pc. Note variant arrays are not the same as an array function

 

[Incogn1to]

Okay I will look into that and come back. Thanks for your quick response!