Whether alternative exists to find method?

nachiketdp

Board Regular
Joined
Jan 31, 2007
Messages
53
Hi,
I have a workbook with a transaction worksheet which contains a list of transactions made in several financial instruments. The composition of this list changes everyday. There is also a master worksheet which contains a list of several instruments available in the market. I am required to ascertain everyday how many of the instruments in transaction worksheet are already present in the master worksheet. If any instrument is not present, I am required to update it's details in the master worksheet else I go ahead.

To ascertain whether the instruments are present in the worksheet I use the following code:-

bqz$ = Trim(Worksheets("Deals").Cells(jj, 7).Value) ' the column of the instrument code is 7th Column
Worksheets("Master").Select
Cells(1, 1).Activate
Set found = Cells.Find(What:=bqz$, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not found Is Nothing Then
Cells.Find(What:=bqz$, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


Now my problem is that since I use the above find method, the macro becomes slower as it has to activate all found cells. I was wondering whether any code exists which will only ascertain whether the instrument is present without activating it. The idea is to maximise the speed of the macro.

Any inputs from you will be highly appreciated !!!

Thanks in advance,
Nachiket Pendharkar.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
Try posting a sample of your data, and what you want to do with it.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hi,

the main problem is "activate"
in the helpfiles, you can see an example to use FIND without activating the cells

please use the CODEtags when posting code

kind regards,
Erik
 

nachiketdp

Board Regular
Joined
Jan 31, 2007
Messages
53
Hi Erik,
Where can I find the helpfiles that you are referring to?are they present on the site of microsoft?

Regards,
Nachiket Pendharkar
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

We are talking about VBA.
So while being in the VBA Editor find the helpmenu or press F1.

quick-trick
click within the code in the word FIND: then press F1
this will bring you straight to the right location
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,160
Messages
5,835,733
Members
430,383
Latest member
Kastore

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
Top