What is the fastest search method?

[ProphetofProfit]

I'm currently running a macro which identifies the value of a cell, then searches for this value in another long list of values, and then does some standard copying and pasting. However, it's taking ages, and I have a lot of cells to scan. I'd like to know which is the fastest way of searching for value in VBA, because what I've got now is unimpressive in it's speed. The code works fine, just a little slow. Would a Do Loop be quicker? I used the method below because I already had some code using the same method which worked fine and I screw up most of the VBA code I write so I played it safe.

Btw the range is from row 2 to row 300, and needs to be searched about 30000 times, which is why the speed is a problem.

1st part:

'Set range
Dim ref
For ref = Sheets("parameters").Range("B13") To Sheets("parameters").Range("B14")
'Importing references
If Sheets("list").Cells(r, 3).Value = Sheets("reference").Cells(ref, 1).Value Then
Sheets("list").Cells(r, 20).Value = Sheets("reference").Cells(ref, 2).Value
End If
Next ref

2nd part:

'Importing numbers back to references
For ref = Sheets("parameters").Range("B13") To Sheets("parameters").Range("B14")
If Sheets("list").Cells(r, 3).Value = Sheets("references").Cells(ref, 1).Value Then
Sheets("references").Cells(ref, 7).Value = Sheets("references").Cells(ref, 7).Value + 1
Sheets("references").Cells(ref, 8).Value = Sheets("references").Cells(ref, 8).Value + Sheets("list").Cells(r, 6).Value
Sheets("references").Cells(ref, 9).Value = Sheets("references").Cells(ref, 9).Value + Sheets("list").Cells(r, 7).Value
Sheets("references").Cells(ref, 10).Value = Sheets("references").Cells(ref, 10).Value + Sheets("list").Cells(r, 31).Value
Sheets("references").Cells(ref, 11).Value = Sheets("references").Cells(ref, 11).Value + Sheets("list").Cells(r, 32).Value
End If
Next ref

 

[ProphetofProfit]

I think this is a clue

From:
http://forum.dzikosoft.com/index.php?topic=22.0

1. Searching worksheet range with Application.Match(string, range, 0): search time 0.043 ms.
(note: the use of Application.WorksheetFunction.Match is faster (35 ms) but less handy for error handling)

2. Search the VBA array with Application.Match(string, array, 0): search time 0.132 ms. So significantly poorer than searching the range.

3. Searching the VBA array with simple custom function that loops through its elements: search time 0.025 ms - the fastest method.

4. Search the worksheet range with custom function that loops through its cells: 1.6 ms. - very slow, as it could be expected.

 

[RichardMGreen]

Once you have the value you're searching for, are you checking each cell in the list individually to see if it's a match?
Can you also give the sheet layout of the data you're searching for and through?

 

[ProphetofProfit]

Once you have the value you're searching for, are you checking each cell in the list individually to see if it's a match?
Can you also give the sheet layout of the data you're searching for and through?

I think I'm checking each cell individually, didn't realise there was any other way to do it.

I'm searching through a list of names in one column, from A2 to A250, each name in the column is a unique value. So in 'list' VBA reads (r,3) where r is the row I've specified and in the cell is a name. Then I ask VBA to search through the list from A2 to A250, until there is a match, and when there is copy and paste some numbers.

Is that what you wanted to know?

 

[RichardMGreen]

Yeah.
You can use the same bit for getting your lookup value and then set an object and use the FIND command to search A2 to A250 to see if it exists.
Something along these lines should speed things up:-

    Dim found As Object, item_to_find As String
    item_to_find = Range("A1")
    Set found = Range("A2:A250").Find(what:=item_to_find, LookIn:=xlValues, lookat:=xlWhole)
    If Not found Is Nothing Then
        'your copy/paste code goes here'
    End If

You can put the whole thing in a loop to change the value of item_to_find.
You can also use the offset command to collect the values you want to copy and change your second bit so that it reads:-
Sheets("references").Cells(ref, 7).Value = found.offset(rowoffset,columnoffset)
your rowoffset will be zero and the columnoffset will be the number of columns you need to move to get the correct value.

Hope that helps.