Setting flag in one workbook based on data in a second workbook

I have a macro with two workbooks open, the macro is running in a separate "control" workbook, all files are in the same folder.

WB1 contains sheet "Setup Sheet" with data for around 1000 club members, with a unique numeric member number in column A, starting at cell A5. The numbers are not in numerical order, and there are no blanks, the number of members will change over time, and the numbers will not be consequtive (missing members who have left).

WB2 contains one sheet ("sheet1") with a subset of the above membership numbers in column A starting at A1, again not in numerical order and with no blanks.

I'm looking for an efficient solution that for each member no. listed in WB2-sheet1 an "X" is inserted into column G in WB-Setup Sheet in the row with the same member no.

If WB2-sheet1 is empty or missing, then the whole bit of code should be skipped. I've got the test for WB2-sheet1 existing, but not if it's empty. Ideally if a member number exists in WB2 but not in WB1 it should flag an error.

I can see ways of doing it by resorting and using something equivalent to a vlookup function, but that seems quite cumbersome. I haven't really thought further about the error handling yet.

For info WB1 is derived from our main membership spreadsheet by the control macro and is then used on a laptop with a bar code reader attached, to register members attending meetings. From time to time we want to be able flag particular members as they register (eg to contact the secretary).

Can anyone help?

Thanks for your "boiler plate" answer! I assume, though you don't say so, that you want me to attach my workbooks!

As explained in my original post there are actually 3 workbooks involved. The description of what I'm trying to achieve has been much simplified, the current macro does lots of other things and has been in use for over a year. I see no point in posting the whole macro, or spending time stripping stuff out to just leave what's relevant to my question, hence me trying to present what I'm wanting to achieve by way of a simple "example". In addition, the data files (WB1 and WB2) contain confidential personal information, so can't be posted on here.

I'm not necessarily looking for someone to write the whole code for me, just maybe suggest an approach that might be more efficient than say 1000 x 30 searches through the files to find the matching pairs.

OK, I've managed to get to a solution that almost works, using the MATCH worksheet function. The key bit of code that works is:

Please Login or Register  to view this content.

It looks for a match with the number FLAGFileNo in the other workbook, in cells A1-A2000 and returns the row number if a match is found, or 0 if not found. With that row number I can set the "X" flag in the relevant place.

The final bit I need to get working is to replace the "A2000" with a variable (LastMemRow) containing the last used row (I've got the code that sets this variable).

I've tried this code:

Please Login or Register  to view this content.

but although the FLAGFileNo and LastMemRow variables contain the correct results, the result MemNoRow is always 0

Maybe I can't use CELLS in this way in a function?

OUCH! I thought I was making it easier to reply by omitting all the rest of the macro which does unrelated things, and of course I can't post workbooks with people's personal data in them.

OK, I was wrong, apologies offered, even if you won't see them.

If anyone's at all interested, this works:

Please Login or Register  to view this content.

i.e. use the original range expression except convert the LastMemRow to a string.