Loop issues - Find value in range that fits value in defined name range

[Sc0tt1e]

I have a ton of data to filter and copy out to new workbooks based on a portfolio code.

I am attempting to loop through "rng" which is the unique numbers from the data, and I am looking it up in "wsd" which is the raw data sheets colum E which holds each lines portfolio code.

This is my first attempt at a loop with vba and I'm having an issue with looking up a range and referencing it against another range. The below code is giving me a runtime error 13 "Type Mismatch" message but I can't figure out why. I have a few ideas but none I can work out how to create a fix. Any suggestions as always welcomed

Please Login or Register  to view this content.

At the moment I am testing but when done K2:K3 becomes "portf" which is a defined name range of 200 odd lines in the G column of sheets "Stats"

[xlbiznes]

Hi,

try this approach of using ado to extract based on a sql statement.

[pareshj]

Hi,

You cannot compare two range like this. You need to check cells wise. You can do something like below:

Please Login or Register  to view this content.

Regards,
Paresh J

[Sc0tt1e]

Paresh

Just to confirm,

I want row one of rng to run through every row of "wds" then once the end has been reached row 2 or "rng" will run through every row of "wds" and so on and so on until "rng" has been exhausted.

Does the code you have provided loop in this order?

By the way you have put

Please Login or Register  to view this content.

would one of them not need to be cell2?

[pareshj]

Yes, exactly it runs the same way as you said. And my apologies, it should be

Please Login or Register  to view this content.

Regards,
Paresh J

[Sc0tt1e]

I'm trying to paste the values copied within the loop to no avail, any ideas of the correct syntax or method?

Please Login or Register  to view this content.

[pareshj]

What exactly you want to paste. Is it a range of cells or single cell?

As your code has changed, you need to modify this part too.

What you need to do is get the union of range of cells when your are in for loop and select that range for copy and the your further code will work.

Regards,
Paresh J

[Sc0tt1e]

I want to run through "rng" one line at a time and have it run through all of "wsd", I want one book to be opened and whenever I have a match between "rng" value and a value in "wds" I want it to copy the line and paste it into wb2.

When the loop has run through all of "wsd" I want the wb2 book to be closed and saved in the "spath" location with the name of the "rng".xlsx value that was being searched.

[Sc0tt1e]

Code as is stands

Please Login or Register  to view this content.