Working code need important changes.

[abjac]

Hi I have below code which I though worked perfect. But after more tests I realize that it can make errors.
Your supposed to be able to search after customer nr, or IBAN. I though if I made the code xlWhole, it needed all digits to find a match, and then I avoided that it would search in wrong column But it can happen.
Other error is it only display on search result, even if more with same numbers.

I want it to be sure it can only search in column C2 and used rows down and column P2 and used rows down.

You input the sheet name example spain. and after the full number. Customer nr is always with 8 digits and leading zeroes.

If I now search in sheet spain with number 00000047 it only display one result even if there is 2 with that number.

If I in sheet france search for number 00017763 it give one result but its from column G. But there could be a customer number which have this number, because of same amounts of digits. So important that it only search in column C2 and down and column P2 and down.

Here is my code and also a little test sheet to work with.

Thanks Sincerely

Abjac

Please Login or Register  to view this content.

[HaHoBe]

Hi, abjac,

maybe just change

Please Login or Register  to view this content.

to read

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Ciao,
Holger

[abjac]

HI Holger and thanks the second option I tried and it works fine. It will only search in these 2 columns . But the other error is still there.

If I in sheet france go for customer number 00007813. It only display one result. But there is 3 rows with that customer nr. Same in spain customer number 00000047 are there 2 times but it only give one result in sheet Result.

Have a look there please.

Sincerely

Abjac

[HaHoBe]

Hi, abjac,

But the other error is still there.

The code you posted only does one search (it will not continue to look for more hits). I wonder why you call that an error as I think the original purpose for the code was to find an unique number/code (IBAN) and not an accountant number/code.

I wonder why you believe you would need to use Find instead of trying to apply the Autofilter with the criteria and copy over if any data is found.

Ciao,
Holger

[abjac]

Hi Holger Your right about that. I thought from the beginning that it was only possible to have one unique number and only the IBAN. But after see the original sheets, I saw there can be duplets. And also that it was necessary to expand it with the customer nr also.
So there I ran in to some problems. I thought first the code was ok if I made it xlWhole, but as seen its not correct.

If you can would really need this to work.

Please if time see if you can change the code to what I described.

Thanks

Abjac

[HaHoBe]

Hi, abjac,

the code you use osnīt what I would call solid and reliable - Iīd go for a UserForm holding the SheetnNames available and a ListBox/ComboBox for the user to make a choice. Same goes for the customer number or Iban being filled on the available data from that sheet. But thatīs my way of coding.

Please Login or Register  to view this content.

Ciao,
Holger

[abjac]

Thanks Holger on my mobile here. Will have and try when home again.
Abjac

[abjac]

Hi Holger home here and just tested it in the big sheet- It works brilliant as usual when you make a code. Thanks so much.
Its really a bigger help than you know.

Have a nice evening

Sincerely
Abjac