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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
Hi Erik,
Where can I find the helpfiles that you are referring to?are they present on the site of microsoft?

Regards,
Nachiket Pendharkar
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,367
Messages
6,119,115
Members
448,870
Latest member
max_pedreira

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