Code is working for ws3,4 and 5 but not 6.

[strafepx]

Hi All,

Got this code which searches a specific column in ws1. Checks it agaisnt ws2 and then copies the row with the matching value and pastes it to its corresponding ws. However it is not working for ws6 even though I know there are matching values in column D and ws2. Can anyone spot why?

Please Login or Register  to view this content.

[rorya]

You need to use code tags when posting code (especially if posting lots of it!).

[strafepx]

Thanks Rory, added now.

[rorya]

Hard to comment without the data. Since you can step through the code, you should be able to determine which part is not doing what you expect - most likely either the StrComp is not matching, or CountIf is not returning 0.

Based on your variable names, you are looking at invoice numbers - are those long numeric strings by any chance?

[strafepx]

Yes that is correct. Unfortunately it won't let me upload test data here. Below are the sets of data I have.


Ws1
AppleAcademics RiverPublishing StanfordPublishing Teton
9780131474932 9780367074272 9780367139995 9780367163679
9780202302607 9780367075453 9780367140830 9780367168698
9780202360546 9780367091392 9780367141141 9780367172848
9780240806884 9780367111632 9780367144395 9780367173876
9780240813301 9780367112356 9780367146757 9780367174200
9780273769293 9780367133597 9780367148652 9780367180508
9780367002527 9780367133610 9780367150044 9780367181017
9780367023911 9780367134631 9780367150266 9780367181376
9780367027797 9780367136987 9780367153700 9780367181376
9780367028909 9780367139919 9780367153885 9780367181376


Ws2
7000272406 1 9780367153885 2 2 2 2 1 1 1 11366520 9 0.27 ENG
7000269020 1 9780367163679 2 2 2 2 1 1 1 11343147 9 0.17 ENG
7000274627 1 9780367168698 2 2 2 2 1 1 1 11380814 9 0.2 ENG
7000277254 1 9780367172848 2 2 2 2 1 1 1 11395952 9 0.18 ENG
7000271632 1 9780367173876 2 2 2 2 1 1 1 11358329 9 0.67 ENG
7000273544 1 9780367174200 2 2 2 2 1 1 1 11375241 9 0.28 ENG
7000276899 1 9780367180508 2 2 2 2 1 1 1 11395652 9 0.18 ENG
7000275823 1 9780367181017 2 2 2 2 35 1 1 11392413 9 10.93 ENG
7000271520 1 9780367181376 2 2 2 2 1 1 1 11357957 9 0.2 ENG
7000272133 1 9780367181376 2 2 2 2 1 1 1 11364195 9 0.2 ENG
7000272133 1 9780367181376 2 2 2 2 1 1 1 11364207 9 0.2 ENG


It should match the values(9780367163679) from column D in ws1 with the values in ws2 then copy the rows and paste them to ws6.

[rorya]

Those should be fine - Excel is limited to 15 significant digits generally.

Unless you can post the workbook, I think you will have to do some debugging yourself to work out which of the tests is not doing what you expect.

[strafepx]

Test data Attached

Had to make file smaller and into a xlsx to upload.

[sintek]

It should match the values(9780367163679) from column D in ws1 with the values in ws2 then copy the rows and paste them to ws6.

Is this only for Teton...or for all the columns A through D

I am very certain that if you explain what it is you are actually wanting to achieve, ignoring your current code, this can easily be simplified...

[rorya]

I don't understand the logic. Why are you looping through every cell in columns A:Z on the Invoices sheet? The reason you aren't getting any matches is because all the invoice numbers are in column C and your code is looking 3 columns to the right of each column, so when it's processing column A, it looks at column D. That means that it never looks at column C in that section.

[strafepx]

"Got this code which searches a specific column in ws1. Checks it agaisnt ws2 and then copies the row with the matching value and pastes it to its corresponding WS. However it is not working for WS6 even though I know there are matching values in column D and WS2. Can anyone spot why?"

Basically I need to search columns A,B,C and D of WS1. I need to locate any matching values in WS2. I then need the row to be copied from WS2 that has the matching value. If that matching value is from column A for example then it needs to be pasted to WS3, B to WS4, C to WS5 and D to WS6. When I have tried the code it has successfully returned the rows for WS3,4 and 5 however for WS6 I get no pasted rows. Considering the code is the same with no changes I cannot understand why it wont work. I am getting no error messages and it is appearing with the text box message "The matching rows have been copied to the appropriate worksheets." when complete. It is almost like it is skipping over the code for column D. Let me know if you need any more detail.

[strafepx]

Invoices received all come in different formats. I need to search from A to Z because depending on the invoice received the column containing the value being searched for can change. Sometimes it is in column A, sometimes B, sometimes C etc

[strafepx]

I can see why it is not working now. no idea why I was offsetting the searches. All in working order now offsetting has been removed. Many thanks all!

[sintek]

I am very certain that this is a simplification...Perhaps I have misunderstood...tested with a header

Please Login or Register  to view this content.