VBA: find last cell with criteria

[icylemontea]

Hi,

I've got the following find code:

Please Login or Register  to view this content.

Sheet1 has customer data, where each row corresponds to an individual. The first column is customerID number and the remaining columns have various customer details. Column 10 contains a date the customer details were valid till.

My problem is, I've noticed that column1 has duplicate numbers due to updated customer details. The find command usually returns the value in the first cell with the stated value (value in sheet2!N1), but I want it to return the last cell which contains that value. Is there a way to tweak the code so that it returns the last cell instead?

Alternatively, the last cell with the value in sheet2!N1 will always have a blank cell (no date) in the same row in column 10. So would it be possible for the find command to find the value of the cell in column 1 which has the value of sheet2!N1 and has a blank cell in the same row in column 10?

Thanks in advance!

[TMS]

Can you post a sample workbook so we can see the layout?

Regards

[icylemontea]

I've attached a workbook with sample data. Please let me know if you don't know what I'm getting at =)
Sorry, noticed I didn't change the layout of sheet 2. I've attached a new workbook.

Many thanks.

[TMS]

I'm a little confused. Have you actually tested this sample workbook?

There are two columns (A and B) called Customer ID on the second sheet which you have called "Pending" but which you refer to, in your code above, as sheet2. There's no code actually in the workbook.

Copying the code above, renaming the worksheet, putting a value into cell N1 and then running the macro puts the First Name into the second Customer ID field.

There's no error handling in the extract of code you have provided so, if the Customer ID isn't found, it will crash.

Are you looking for someone to fix the code first before enhancing it?

Regards

[icylemontea]

Sorry about that. It's because this was just sample data I created, so there were a few typos.
I've amended the workbook above. Since I don't have the actual workbook with me at the moment, I had to do things from memory. The code I provided is only a small part of the full thing. Ultimately, I want to find and return all the fields in each column of the individual with the user ID in cell sheet2 N2.

If you look at sheet 1 there are two rows with customerIDs 1230. The second row contains the updated data from 20/01/2004 and it is the fields in this row I want the 'Find' procedure to return, but the code I have provided returns the fields in the first row.

I hope it's clearer now.
Thanks

[contaminated]

Hi. Is that what you want???
Used formula

=IF(COUNTIF(Sheet1!$A$2:$A$40,$N$1)<ROWS($A$1:$A1),"",INDEX(Sheet1!A$2:A$40,SMALL(IF($N$1=Sheet1!$A$2:$A$40,ROW(INDIRECT("1:"&ROWS(Sheet1!$A$2:$A$40)))),ROW(A1))))

confirmed with Ctrl+Shift+Enter, then copied down and right.

[icylemontea]

thanks contaminated.
Is there any way to get it using vba? It would be great if it can be done by tweaking the find command. It's only because this is only a small step of a really lengthy vba procedure. I need this to be done automatically when a user clicks a button since the amount of data I have is over 500 rows

[Leith Ross]

Hello icylemontea,

This macro is probably closer to what you need. This will find the last recurring customer id and copy the row to Sheet2 row 3. I added a button on Sheet2 to run the macro. This macro has been added to Module1 in the attached workbook.

I could have expanded the Find method to check for recurring entries. This method of using a Dictionary object is both faster and more flexible, despite the additional code.

Please Login or Register  to view this content.