Is there a faster VBA search option in Excel than NEXT loops?

[gtaborda]

Hi there

I have two excel sheets open.

With VBA code, I search+replace for all rows on the "mainsheet" to look for matching data on "data_sheet" and, if found, data is updated on the "mainsheet".
This works well but it is too slow.

I do the search+replace with next loops like this:

for x=1 to 10000 ' mainsheet rows

for z=1 to 1000 ' data_sheet rows
if mainsheet.range("A" & x)=data_sheet.range("A" & z) and mainsheet.range("B" & x)=data_sheet.range("B" & z) THEN
mainsheet.range("C" & x)=data_sheet.range("C" & z)
end if

next z
next x

It is a bit more complex than that but more or less that's the routine.
Like I said, it works, but because of the double loop, it takes a while, specially if the sheets have a lot of rows.

On the example above I put 2 cell match, however sometimes it has to match up to 5 cells.

The question is, is there a faster/better way to do this kind of global updates with VBA between two sheets ?

basically I need to do like an Query in Access (SQL), which is very fast, but with Excel.

Any ideas/comments will be appreciated.

thanks in advance

 

[taupirho]

You would probably be better off using one or more of the lookup functions. Check out the following

match
lookup
indirect
hlookup
vlookup


In order to understand recursion, you must first understand recursion.

 

[PHV]

A common way to avoid non necessary iterations:
if mainsheet.range("A" & x)=data_sheet.range("A" & z) and mainsheet.range("B" & x)=data_sheet.range("B" & z) THEN
mainsheet.range("C" & x)=data_sheet.range("C" & z)
[!]Exit For[/!]
end if


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gtaborda]

hi there
thanks both for answering

taupirho, I will check the lookup functions you mention...I learned programming from Basic many years ago and I guess I am stuck using for-next loops...

PHV, thanks for your comment, yes, I do use Exit For to stop searching once match is found, I just didn't type the whole code, just an example...

Than you both.

 

[xlbo]

Could also look at using a single loop in combination with the FIND method

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gtaborda]

Hi there,

I've looked into the FIND method...I've search on the Internet and I was only able to find that it searches for 1 cell at a time...

In most cases I have to look for up to 5 cells per row (columns A,B,C,D)...if it matches then it puts the value of E & F on the mastersheet...

How can the FIND method look for several values on different cells as it loops through ???

thanks

 

[Gavona]

Advanced filter can efficiently match multiple conditions.

As you step through your data copy the values in An:dn to a criteria range then apply advanced filter and check if any records are displayed

With range("criteria") defined as a single cell then

    Range(???????).Copy Range("Criteria").Offset(1,0)
    Range("alldata").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Range("Criteria").CurrentRegion, _
        CopyToRange:=Range("output"), _
        Unique:=False

You could Output.CurrentRegion.rows.count to see if there is a match for that criteria....

Gavin