VBA help-Look up previous day data and return result

[Natalia]

Hi,

I need your help with a worktask, not a big task. I have reconciliation reports which gets updated each day, so some entries will clear and some will stay and there will be new entries appearing. My team enter their names for the recs they are responsible for as shown in 050511 wkb. The next day when the new report is updated they would have to re-input their names in A6:A to their recs.

I need a macro that will compare current data to previous day if match found then return their names in Col A6:A. This will enable them to see which breaks haven't cleared.

Now there are two tabs "Cash" and "Asset", the asset tab is easy to do a lookup as there is a case number in E6:E, but for cash the macro will need to concatenate in VBA memory a few columns to create a unique ref. So to concatenate cash use B&C&D&E&F&G&H&I&L&M&N, this should do it.

050511 Workbook

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5	Name	Group	Category	Set ID	Value Date	Entry Date	Type	Amount	CCY	Age	Source	Ref1	Ref2	Ref3	Reference
6	Nat	CITIBANK	CASH CITI	BNPPSITESAPP	2-Jun-08	2-Dec-09	SCR	4,417.80	GBP	1019	SMP1	FUNDS TFR	NATL WESTMINSTER BK PLC LONDON	SITE ROB GREGOR
7	James	CITIBANK	CASH CITI	BNPPSITESAPP	3-Jun-08	2-Dec-09	SCR	2,208.90	GBP	1018	SMP1	FUNDS TFR	CITIBANK NA LONDON	JEFFERY NEIL 3 GOLDERSL
8	John	CITIBANK	CASH CITI	WTSIE1GBPBNPP	20-Dec-10	20-Dec-10	SCR	5,834.29	GBP	88	SMP1	NONREF	SG1035400992OMTS	AMP CAPITAL INVESTORS	kjhjhjhjh
9	Louise	CITIBANK	CASH CITI	WTPE2AUSDBNPP	23-Dec-10	23-Dec-10	SCR	6,250.00	USD	85	SMP1	634	SG1035700026OMTS	CETERA BROKERS NETWORK, LLC
10	James	CITIBANK	CASH CITI	WTGDPFEURBNPP	24-Jan-11	24-Jan-11	SDR	-2,200,000.00	EUR	53	SMP1	FXWTDPFP22	SG1102400435OMTS	ALLEZ AND ASSOCIES SCP MARTIN
Cash

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
5	Name	Rec	Account	Source	Case No.	Value Date	Security Code	Description	Ccy Code	Ledg Stmt	Units	Exception	Age Break
6	Nat	BNPPINTL	BNPP.WTPE1A	AMP1	773537	4-Apr-11	SOLCAUSD	SOLYNDRA INC CONVERTIBLE NOTE TRANCHE A	USD	Ledg	1,003,682.00	-1,003,682.00	29
7	James	BNPPINTL	BNPP.WTPE1B	AMP1	773538	4-Apr-11	SOLCAUSD	SOLYNDRA INC CONVERTIBLE NOTE TRANCHE A	USD	Ledg	177,120.00	-177,120.00	29
8	John	SMP	DIAT.DAI	AMP2	773875	8-Apr-11	AU3CB0173953	MEDL 2011-1 A2 6.5PCT 22NOV42		Stmt	1,000,000.00	1,000,000.00	25
9	Louise	SMP	CITA.CBAS	AMP2	774234	12-Apr-11	AU000000EQN4	EQUINOX MINERALS CDI NPV /ST/010		Stmt	59,782.00	59,782.00	21
10	Louise	SMP	CITA.CBAS	AMP2	774235	12-Apr-11	AU000000CGG3	CITADEL RESOURCE GROUP LIMITED NPV /ST/010		Stmt	854,892.00	-854,892.00	21
11	Louise	SMP	NCSX.GESBE	AMP2	774944	15-Apr-11	NCM	NEWCREST MINING LIMITED	AUD	Ledg	465,027.00	1,200.00	18
12	Louise	SMP	NCSX.GESBE	AMP2	774944	15-Apr-11	AU000000NCM7	NEWCREST MINING LTD	AUD	Stmt	466,227.00		18
13	Louise	SMP	JPMT.WHEISS	AMP2	775097	18-Apr-11	B1FYW63	GAGFAH SA	EUR	Ledg	8,547.00	-8,547.00	15
14	Louise	SMP	NCSX.CSL	AMP2	775233	19-Apr-11	WHG	WHK GROUP LIMITED	AUD	Ledg	80,578.00	-80,578.00	14
15	Louise	SMP	NCSX.GESS	AMP2	775232	19-Apr-11	WHG	WHK GROUP LIMITED	AUD	Ledg	126,002.00	-126,002.00	14
Asset

060511 Workbook

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5	Name	Group	Category	Set ID	Value Date	Entry Date	Type	Amount	CCY	Age	Source	Ref1	Ref2	Ref3	Reference
6		CITIBANK	CASH CITI	BNPPSITESAPP	2-Jun-08	2-Dec-09	SCR	4,417.80	GBP	1019	SMP1	FUNDS TFR	NATL WESTMINSTER BK PLC LONDON	SITE ROB GREGOR
7		JP MORGAN	CASH JP	YTYTTYTT	12-Aug-10	7-Nov-10	SCR	23,434.36	BNM	345	WER	YTYTY	244	544
8		CITIBANK	CASH CITI	BNPPSITESAPP	3-Jun-08	2-Dec-09	SCR	2,208.90	GBP	1018	SMP1	FUNDS TFR	CITIBANK NA LONDON	JEFFERY NEIL 3 GOLDERSL
9		CITIBANK	CASH CITI	WTSIE1GBPBNPP	20-Dec-10	20-Dec-10	SCR	5,834.29	GBP	88	SMP1	NONREF	SG1035400992OMTS	AMP CAPITAL INVESTORS	kjhjhjhjh
10		CITIBANK	CASH CITI	WTPE2AUSDBNPP	23-Dec-10	23-Dec-10	SCR	6,250.00	USD	85	SMP1	634	SG1035700026OMTS	CETERA BROKERS NETWORK, LLC
11		CITIBANK	CASH CITI	WTGDPFEURBNPP	24-Jan-11	24-Jan-11	SDR	-2,200,000.00	EUR	53	SMP1	FXWTDPFP22	SG1102400435OMTS	ALLEZ AND ASSOCIES SCP MARTIN
12
Cash

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
5	Name	Rec	Account	Source	Case No.	Value Date	Security Code	Description	Ccy Code	Ledg Stmt	Units	Exception	Age Break
6		JP MORGAN	CITA.CBAS	CIT1	234233	8-Apr-11	EWREWTEW	Test	NOR	Ledg	3,235,325.00	-12,155.00	21
7		BNPPINTL	BNPP.WTPE1A	JP1	773537	4-Apr-11	SOLCAUSD	SOLYNDRA INC CONVERTIBLE NOTE TRANCHE A	USD	Ledg	1,003,682.00	-1,003,682.00	29
8		BNPPINTL	BNPP.WTPE1B	JP1	773538	4-Apr-11	SOLCAUSD	SOLYNDRA INC CONVERTIBLE NOTE TRANCHE A	USD	Ledg	177,120.00	-177,120.00	29
9		SMP	DIAT.DAI	JP1	773875	8-Apr-11	AU3CB0173953	MEDL 2011-1 A2 6.5PCT 22NOV42		Stmt	1,000,000.00	1,000,000.00	25
10		SMP	CITA.CBAS	JP1	774234	12-Apr-11	AU000000EQN4	EQUINOX MINERALS CDI NPV /ST/010		Stmt	59,782.00	59,782.00	21
11		SMP	CITA.CBAS	JP1	774235	12-Apr-11	AU000000CGG3	CITADEL RESOURCE GROUP LIMITED NPV /ST/010		Stmt	854,892.00	-854,892.00	21
12		SMP	NCSX.GESBE	JP1	774944	15-Apr-11	NCM	NEWCREST MINING LIMITED	AUD	Ledg	465,027.00	1,200.00	18
13		SMP	NCSX.GESBE	JP1	774944	15-Apr-11	AU000000NCM7	NEWCREST MINING LTD	AUD	Stmt	466,227.00		18
14		SMP	JPMT.WHEISS	JP1	775097	18-Apr-11	B1FYW63	GAGFAH SA	EUR	Ledg	8,547.00	-8,547.00	15
15		SMP	NCSX.CSL	JP1	775233	19-Apr-11	WHG	WHK GROUP LIMITED	AUD	Ledg	80,578.00	-80,578.00	14
16		SMP	NCSX.GESS	JP1	775232	19-Apr-11	WHG	WHK GROUP LIMITED	AUD	Ledg	126,002.00	-126,002.00	14
17		SMP	NCSX.GESS	JP1	151511	19-Apr-11	WHG	WHK GROUP LIMITED	AUD	Ledg	126,002.00	-54,545.32	14
Asset

 

[bertie]

Consider TODAY’s workbook to be the NEW workbook
Consider YESTERDAY’s workbook to be the OLD workbook

The variable rngAssetNew represents a range in the NEW workbook.
The initial range is set to cell “B6”.

Set rngAssetNew = Sheets("Asset").Range("B6")​

It then loops until it find finds a cell in column “B” which is empty,

Do Until rngAsetNew=””

‘rest of the code​

Loop​

The same applies to looping through the OLD workbook.
Once the code finds a cell in column "B" which is empty the macro ends.

 

[Natalia]

Thanks Bertie, i found out what it was if column B has #N/A then the code returned a Type mismatch 13 error.

The macro takes 2 mins to run on full data, the cash has about 3000 rows of data and asset has about 2000, can this be sped up? I tried putting the below but it was still slow.

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Consider TODAY’s workbook to be the NEW workbook
Consider YESTERDAY’s workbook to be the OLD workbook

The variable rngAssetNew represents a range in the NEW workbook.
The initial range is set to cell “B6”.

Set rngAssetNew = Sheets("Asset").Range("B6")​

It then loops until it find finds a cell in column “B” which is empty,
<b>

Do Until rngAsetNew=””

‘rest of the code​

Loop​

</b>

The same applies to looping through the OLD workbook.
Once the code finds a cell in column "B" which is empty the macro ends.

 

[Natalia]

Bertie, the highlighted names are not being picked up by the macro when i run the code in 060511

050511 Workbook

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
5	Name	Rec	Account	Source	Case No.	Value Date	Security Code	Description	Ccy Code	Ledg Stmt	Units	Exception	Age Break
6		TEST	TEST	TEST	752133	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
7		TEST	TEST	TEST	766330	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
8	Mark	TEST	TEST	TEST	767284	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
9		TEST	TEST	TEST	767290	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
10		TEST	TEST	TEST	767290	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
11	Dale	TEST	TEST	TEST	767290	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
12		TEST	TEST	TEST	767290	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
13		TEST	TEST	TEST	767291	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
14		TEST	TEST	TEST	767291	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
15	Dale	TEST	TEST	TEST	767291	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
16	Dale	TEST	TEST	TEST	767295	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
17		TEST	TEST	TEST	767298	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
Asset

060511 Workbook

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
5	Name	Rec	Account	Source	Case No.	Value Date	Security Code	Description	Ccy Code	Ledg Stmt	Units	Exception	Age Break
6		TEST	TEST	TEST	752133	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
7		TEST	TEST	TEST	766330	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
8	Mark	TEST	TEST	TEST	767284	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
9		TEST	TEST	TEST	767290	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
10		TEST	TEST	TEST	767290	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
11		TEST	TEST	TEST	767290	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
12		TEST	TEST	TEST	767290	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
13		TEST	TEST	TEST	767291	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
14		TEST	TEST	TEST	767291	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
15		TEST	TEST	TEST	767291	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
16	Dale	TEST	TEST	TEST	767295	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
17		TEST	TEST	TEST	767298	TEST	TEST	TEST	TEST	TEST	TEST	TEST	TEST
Asset

 

[bertie]

Hi Nat,

Regarding the speed issue.

The quickest way to do this would be to use formula. You mentioned in an earlier post that you could use a vLookup to process the Asset sheet. Have you considered using Index Match to process the Cash sheet?

Use a spare column, i.e., "P" for your concatenation formula.
Place the Index Match formula in column "A".

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
5	Name	Group	Category	Set ID	Value Date	Entry Date	Type	Amount	CCY	Age	Source	Ref1	Ref2	Ref3	Reference	*
6	Nat	CITIBANK	CASH CITI	BNPPSITESAPP	02-Jun-08	02-Dec-09	SCR	4,417.80	GBP	1019	SMP1	FUNDS TFR	NATL WESTMINSTER BK PLC LONDON	SITE ROB GREGOR	*	CITIBANKCASH CITIBNPPSITESAPP39601401494417.8GBPSMP1FUNDS TFRSITE ROB GREGOR
Cash

This will certainly be quicker than looping through thousands of rows.

I will be away for the rest of the day (Christening). I will look at the other issues when I get back.

 

[Natalia]

Hi Bertie,

I have no issue in using formula but this task is daily and i want to avoid manual input of formulas everyday. I like the idea of the macro asking to choose file and then the code compares the data.

If there is Vba method that can use formula input and allow the choosing of file then i would welcome that method.

 

[bertie]

Hi Nat,

To speed up the macro I have taken out the second loop and used the FIND method instead.

This works fine with the ASSET sheet as it has a unique identifier in column "E", CASE NO. If this is found we get the row it is on and update the username from column "A" on the same row.

The only way this will work for the CASH sheet will be if there is a unique identifier here also. For testing I have used column "P" to hold the concatenation string derived from a formula; see my earlier post. This column can be hidden. Again, if a match is found we get the username from comumn "A" on the same row.

[COLOR=darkblue]Sub[/COLOR] CompareStrings()
   [COLOR=darkblue]Dim[/COLOR] wbOld [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] sMatch [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rngAssetNew [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] rngCashNew [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] FileToOpen
   [COLOR=darkblue]Dim[/COLOR] rngRow As Range
 
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errCompareStrings
 
   [COLOR=green]'============================================[/COLOR]
   [COLOR=green]'set up the worksheet ranges to loop through[/COLOR]
   [COLOR=green]'============================================[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngAssetNew = Sheets("Asset").Range("B6")
   [COLOR=darkblue]Set[/COLOR] rngCashNew = Sheets("Cash").Range("B6")
 
   FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
   [COLOR=darkblue]If[/COLOR] FileToOpen <> [COLOR=darkblue]False[/COLOR] [COLOR=darkblue]Then[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wbOld = Workbooks.Open(FileToOpen)
   [COLOR=darkblue]Else[/COLOR]
      MsgBox "No file Selected"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
   [COLOR=green]'================[/COLOR]
   [COLOR=green]'asset worksheet[/COLOR]
   [COLOR=green]'================[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngAssetNew = ""
      sMatch = Trim(rngAssetNew.Offset(0, 3).Value)
 
      [COLOR=green]'in case no match is found[/COLOR]
      [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
         [COLOR=darkblue]With[/COLOR] wbOld.Sheets("Asset")
[COLOR=red]         Set rngRow = .Columns(5).Find( _[/COLOR]
[COLOR=red]            What:=sMatch, _[/COLOR]
[COLOR=red]            After:=.Cells(5, 5), _[/COLOR]
[COLOR=red]            LookIn:=xlValues, _[/COLOR]
[COLOR=red]            LookAt:=xlPart, _[/COLOR]
[COLOR=red]            SearchOrder:=xlByColumns, _[/COLOR]
[COLOR=red]            SearchDirection:=xlNext, _[/COLOR]
[COLOR=red]            MatchCase:=False, _[/COLOR]
[COLOR=red]            SearchFormat:=False)[/COLOR]
 
            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rngRow [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
               rngAssetNew.Offset(0, -1).Value = .Range("A" & rngRow.Row).Value
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
 
      [COLOR=green]'next entry[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rngAssetNew = rngAssetNew.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]
[COLOR=green]'Stop[/COLOR]
   '================
   [COLOR=green]'cash worksheet[/COLOR]
   [COLOR=green]'================[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngCashNew = ""
      sMatch = Trim(rngCashNew.Offset(0, 14).Value)
      [COLOR=green]'in case no match is found[/COLOR]
      [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
         [COLOR=darkblue]With[/COLOR] wbOld.Sheets("[COLOR=red]Cash")[/COLOR]
            [COLOR=darkblue]Set[/COLOR] rngRow = .Columns[COLOR=red](16).[/COLOR]Find( _
               What:=sMatch, _
               After:=.Cells([COLOR=red]5, 16),[/COLOR] _
               LookIn:=xlValues, _
               LookAt:=xlPart, _
               SearchOrder:=xlByColumns, _
               SearchDirection:=xlNext, _
               MatchCase:=False, _
               SearchFormat:=False)
 
            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rngRow [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
               rngCashNew.Offset(0, -1).Value = .Range("A" & rngRow.Row).Value
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
 
      [COLOR=green]'next entry[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rngCashNew = rngCashNew.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]
 
exitCompareStrings:
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
  [COLOR=green]' wbOld.Close SaveChanges:=False[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wbOld = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngAssetNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngCashNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngRow = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
errCompareStrings:
   MsgBox "Error: " & Err.Number & vbCrLf & _
         "Description: " & Err.Description
   [COLOR=darkblue]Resume[/COLOR] exitCompareStrings:
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Natalia]

Hi Bertie,

This is heaps faster, it took 12 seconds when i added the screenupdating etc.

I have implemented the concatenation column and this is hidden.

Thanks for your time and help on this task

 

[Natalia]

Hi Bertie,

How can the below be amended to lookup two columns and return result in one worksheet. So return result in Col A and Col B. Unique identifier is Col I, i tried amending your code but it didn't return all result in Col B, missed a few comments. I have provided to screen shots below, one shows previous day data and the other today data.

Excel Workbook
	A	B	C	D	E	F	G	H	I
4	Previous Day
5	Name	Comments	Pfolio	Broker	ContractDate	SettleDate	DaysOutstanding	EntryDate	TransID
6	Joe	Test1	WSTIEF	DIV	2-Jul-09	6-Jul-09	695	2-Jan-10	23943
7			PISIFE	TAKEOV	6-Aug-10	13-Aug-10	302	6-Aug-10	4592611
8			PISIFE	TAKEOV	6-Aug-10	13-Aug-10	302	6-Aug-10	4592612
9			2YEMXE	DIV	13-Sep-10	11-Oct-10	233	2-Jan-11	267310
10			2EMXXS	DIV	13-Sep-10	11-Oct-10	233	2-Jan-11	267253
11	James	Test2	2YEGXE	DIV	27-Sep-10	12-Oct-10	232	2-Jan-11	267307
12	James	Test3	2YEGXE	DIV	27-Sep-10	12-Oct-10	232	2-Jan-11	267317
13	James	Test4	2EGXXS	DIV	27-Sep-10	12-Oct-10	232	2-Jan-11	267242
14	James	Test5	2EGXXS	DIV	27-Sep-10	12-Oct-10	232	2-Jan-11	267316
15			OPSINN	DIV	24-Feb-11	18-May-11	14	24-Feb-11	4923781
Asset

Excel Workbook
	A	B	C	D	E	F	G	H	I
5	Name	Comments	Pfolio	Broker	ContractDate	SettleDate	DaysOutstanding	EntryDate	TransID
6			WSTIEF	DIV	2-Jul-09	6-Jul-09	695	2-Jan-10	23943
7			PISIFE	TAKEOV	6-Aug-10	13-Aug-10	302	6-Aug-10	4592611
8			PISIFE	TAKEOV	6-Aug-10	13-Aug-10	302	6-Aug-10	4592612
9			2YEMXE	DIV	13-Sep-10	11-Oct-10	233	2-Jan-11	267310
10			2EMXXS	DIV	13-Sep-10	11-Oct-10	233	2-Jan-11	267253
11			2YEGXE	DIV	27-Sep-10	12-Oct-10	232	2-Jan-11	267307
12			2YEGXE	DIV	27-Sep-10	12-Oct-10	232	2-Jan-11	267317
13			2EGXXS	DIV	27-Sep-10	12-Oct-10	232	2-Jan-11	267242
14			2EGXXS	DIV	27-Sep-10	12-Oct-10	232	2-Jan-11	267316
15			OPSINN	DIV	24-Feb-11	18-May-11	14	24-Feb-11	4923781
Asset

  '============================================
   'set up the worksheet ranges to loop through
   '============================================
 
   Set rngAssetNew = Sheets("Asset").Range("C6")

 
   FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
   If FileToOpen <> False Then
      Set wbOld = Workbooks.Open(FileToOpen)
   Else
      MsgBox "No file Selected"
      Exit Function
   End If
 
   '================
   'asset Worksheet
   '================
   Do Until rngAssetNew = ""
      sMatch = Trim(rngAssetNew.Offset(0, 6).Value)

      'in case no match is found
      On Error Resume Next
         With wbOld.Sheets("Asset")
         Set rngRow = .Columns(9).Find( _
            What:=sMatch, _
            After:=.Cells(5, 9), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)

            If Not rngRow Is Nothing Then
               rngAssetNew.Offset(0, -2).Value = .Range("A" & rngRow.Row).Value
            End If
         End With
      On Error GoTo 0

      'next entry

   Loop

   Do Until rngAssetNew = ""
     Set rngAssetNew = rngAssetNew.Offset(1, 0)
            sMatch = Trim(rngAssetNew.Offset(0, 6).Value)

      'in case no match is found
      On Error Resume Next
         With wbOld.Sheets("Asset")
         Set rngRow = .Columns(9).Find( _
            What:=sMatch, _
            After:=.Cells(5, 9), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)

            If Not rngRow Is Nothing Then
               rngAssetNew.Offset(0, -1).Value = .Range("B" & rngRow.Row).Value
            End If
         End With
      On Error GoTo 0

      'Next entry
      Set rngAssetNew = rngAssetNew.Offset(1, 0)
   Loop