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.
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.