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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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