Using VBA (or excel) to

[twinkle99]

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


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

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
23
Sheet1

 

[jindon]

Hi

Can you show us your desired result out of your data posted?

 

[twinkle99]

Using VBA (or excel) to identify identical rows

Ok, well say for example i wanted to find the amount which related to member reference 1021 and element INCV as at 01/08/2001.

So 1021 and 01/08/2001 are input into another sheet by the user.

The Macro will then find the row which has the text string 1021#INCV. Once it finds it, it will then find the corresponding date in Column D. If will then check if the date in D is before 01/08/2001 and if it isnt it should move to the next text string 1021#INCV until it finds the right row. The correct row in this case is A12. Once it finds the correct one, it then posts back the result in Column C - 950.

Hope that helps

 

[jindon]

Have you tried Find Method?
try

Sub test()
Dim r As Range, ff As String, myTxt, myDate As Date
myTxt = InputBox("Enter value to search")
Do
   myDate = InputBox("Enter date")
   If Not IsDate(myDate) Then MsgBox "Enter in a date format"
Loop Until IsDate(myDate)
With Range("a1",Range("a" & Rows.Count).End(xlUp))
   Set r = .Find(myTxt,.Cells(.Cells.Count),,xlWhole)
   If Not r Is Nothing Then
      ff = r.Address
      Do
         If r.Offset(,4).Value <= myDate Then
            MsgBox "Found In Row " & r.Row
            Exit Sub
        End If
        Set r = .FindNext(r)
      Loop Until ff = r.Address
      MsgBox "No matching data found"
   End If
End With
End Sub