increasing code efficiency/speed

mrFlibble47

New Member
Joined
Aug 24, 2007
Messages
40
Hi,

I have the following code that matches the entries in column A against those in column C and prints "match" in the adjacent cell in column B when the entry in A is present somewhere in column C.

Code:
Sub macro1()


Dim i As Integer
Dim j As Integer

i = 0

Do

i = i + 1

Range("B1") = i

j = 1

    Do
    
    j = j + 1
    
    If Cells(i, 1) = Cells(j, 3) Then Cells(i, 2) = "Match"
    
    Loop Until Cells(j, 3) = ""


Loop Until Cells((i + 1), 1) = ""


End Sub

what i'm wondering is, is there a way of making this run much faster (other than stopping screen updating) as when there are several thousand entries in each column it can take a fair while to run. I'm thinking maybe there's an inbuilt function in excel that I am not aware of that could be used.
One possibility I just thought of is getting the 2nd loop to stop when a match is found rather than run through the entire list in column C as it currently does.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Why not just put a formula in column B?

In B1
=IF(ISNUMBER(MATCH(A1,C:C,0)),"Match","")
Fill down..

Hope this helps...
 
Upvote 0
If it must be code...

Code:
Sub Test()
Dim i as long, X as long, LR as Long
 
' Finds last used row in column A
LR =Cells(Rows.Count,"A").End(xlup).Row
 
For i = 1 to LR
    X = Application.Match(Cells(i,"A").Value, Range("C:C"), 0)
    If Not IsError(X) Then Cells(i, "B").Value = "Match"
Next i
End Sub

HTH
 
Upvote 0
That is exactly the kind of thing I was after. It produced exactly the same result as my macro in a tiny tiny fraction of the time the macro took. I knew there'd be something that did the job a lot quicker.

Thanks very much :)
 
Upvote 0
=IF(ISNUMBER(MATCH(A1,C:C,0)),"Match","")

That is exactly the kind of thing I was after. It produced exactly the same result as my macro in a tiny tiny fraction of the time the macro took. I knew there'd be something that did the job a lot quicker.

Thanks very much :)


edit: oops meant to edit not post a new one
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top