Using VBA (or excel) to

twinkle99

Board Regular
Joined
Aug 7, 2005
Messages
240
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
ABCD
11021#INCV160018/09/2005
21021#CA042018/09/2005
31021#ADP62018/09/2005
41021#INCV145220/09/2004
51021#CA041520/09/2004
61021#ADP8820/09/2004
71021#CA049017/09/2003
81021#ADP3317/09/2003
91021#INCV120017/09/2003
101021#INCV116616/08/2002
111021#INCV102015/08/2001
121021#INCV95016/01/2000
131569#INCV156018/09/2005
141569#CA0520018/09/2005
151569#ADP61518/09/2005
161569#INCV150020/09/2004
171569#CA0515020/09/2004
181569#ADP59520/09/2004
191569#CA059017/09/2003
201569#ADP50017/09/2003
211569#INCV140017/09/2003
221569#INCV133312/08/2002
23
Sheet1
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

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

twinkle99

Board Regular
Joined
Aug 7, 2005
Messages
240
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Have you tried Find Method?
try
Code:
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
 

Forum statistics

Threads
1,136,266
Messages
5,674,728
Members
419,523
Latest member
Urnovio

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
Top