I have a large data file which has thousands of rows of data.
In Column A is a text string which is the reference number followed by an element. In Column C is the amount of that element and Column D is the date of that element.
I have written a macro which reads through the data file and finds the data which is relevant to the reference number and date both of which are inputted on another sheet (not supplied).
The problem I have is that in Column A, there may be rows which are identical. The macro looks for the text string, however, I dont know how to get the Macro to distinguish between the identical rows - i.e. 1021#INCV.
An example of the VBA for one input is:
With Application.WorksheetFunction
a.Range("C10") = .Index(b.Range("C:C"), .Match(b.Range("C11").Value & "#INCV", s, 0))
C:C - is the result that I want - the amount
C11 - is the reference number
s - is Column A:A where the text string will be
Basically, the macro should find the text string, check whether the date in D:D is before the calculation date (input on another sheet) and if it isnt, it should move to the next string and check the date etc until it finds the one which is before. However, it doesnt work because the macro just finds the first line every time (because the other INCV's are identical)
Is there a way of either writing an excel formula in Column B which will rename the identical rows when they occur i.e. 1021#INCV2, then 1021#INCV3 etc. Note that the elements for each member can appear in any row and are not necessarily in the same place, The dates and amounts will also be different. So members may return more rows than others. If this is possible I can just look for the different text strings in the macro.
Alternatively, is there a way of doing something in the Macro?
I have supplied a very short piece of the data file.
Thanks
In Column A is a text string which is the reference number followed by an element. In Column C is the amount of that element and Column D is the date of that element.
I have written a macro which reads through the data file and finds the data which is relevant to the reference number and date both of which are inputted on another sheet (not supplied).
The problem I have is that in Column A, there may be rows which are identical. The macro looks for the text string, however, I dont know how to get the Macro to distinguish between the identical rows - i.e. 1021#INCV.
An example of the VBA for one input is:
With Application.WorksheetFunction
a.Range("C10") = .Index(b.Range("C:C"), .Match(b.Range("C11").Value & "#INCV", s, 0))
C:C - is the result that I want - the amount
C11 - is the reference number
s - is Column A:A where the text string will be
Basically, the macro should find the text string, check whether the date in D:D is before the calculation date (input on another sheet) and if it isnt, it should move to the next string and check the date etc until it finds the one which is before. However, it doesnt work because the macro just finds the first line every time (because the other INCV's are identical)
Is there a way of either writing an excel formula in Column B which will rename the identical rows when they occur i.e. 1021#INCV2, then 1021#INCV3 etc. Note that the elements for each member can appear in any row and are not necessarily in the same place, The dates and amounts will also be different. So members may return more rows than others. If this is possible I can just look for the different text strings in the macro.
Alternatively, is there a way of doing something in the Macro?
I have supplied a very short piece of the data file.
Thanks
vba test.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | 1021#INCV | 1600 | 18/09/2005 | |||
2 | 1021#CA04 | 20 | 18/09/2005 | |||
3 | 1021#ADP | 620 | 18/09/2005 | |||
4 | 1021#INCV | 1452 | 20/09/2004 | |||
5 | 1021#CA04 | 15 | 20/09/2004 | |||
6 | 1021#ADP | 88 | 20/09/2004 | |||
7 | 1021#CA04 | 90 | 17/09/2003 | |||
8 | 1021#ADP | 33 | 17/09/2003 | |||
9 | 1021#INCV | 1200 | 17/09/2003 | |||
10 | 1021#INCV | 1166 | 16/08/2002 | |||
11 | 1021#INCV | 1020 | 15/08/2001 | |||
12 | 1021#INCV | 950 | 16/01/2000 | |||
13 | 1569#INCV | 1560 | 18/09/2005 | |||
14 | 1569#CA05 | 200 | 18/09/2005 | |||
15 | 1569#ADP | 615 | 18/09/2005 | |||
16 | 1569#INCV | 1500 | 20/09/2004 | |||
17 | 1569#CA05 | 150 | 20/09/2004 | |||
18 | 1569#ADP | 595 | 20/09/2004 | |||
19 | 1569#CA05 | 90 | 17/09/2003 | |||
20 | 1569#ADP | 500 | 17/09/2003 | |||
21 | 1569#INCV | 1400 | 17/09/2003 | |||
22 | 1569#INCV | 1333 | 12/08/2002 | |||
Sheet1 |