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

[davidm_uk]

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?

[protonLeah]

To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[davidm_uk]

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.

[davidm_uk]

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:

Dim LastMemRow As Integer
Dim FLAGFileRow As Integer
Dim FLAGFileNo As Integer
Dim MemNoRow As Integer

MemNoRow = WorksheetFunction.Match(FLAGFileNo, Workbooks(TalkRegTempFile).Worksheets("Setup Sheet").Range("A1", "A2000"), 0)

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:

MemNoRow = WorksheetFunction.Match(FLAGFileNo, Workbooks(TalkRegTempFile).Worksheets("Setup Sheet").Range(Cells(1, 1), Cells(1, LastMemRow)), 0)

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?

[protonLeah]

1. In post #1 you made vague descriptions of worksheets and hints of an unposted macro which operates on various vague and unorganized data.
2. I used a standard forum reply to ask for sample workbooks and you are offended? The forum uses standard replies so we don't have to compose unique replies for situations that are constant and recurring.
3:

The description of what I'm trying to achieve has been much simplified

Don't expect assistance without accurate descriptions...

Then there's:

the current macro does lots of other things

Another hint of some mysterious, unposted macro...



I asked for the workbook because I was interested in helping; that was a mistake, all I got was a slap in the face. Don't bother to reply, I won't be revisiting.

[davidm_uk]

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.

[davidm_uk]

If anyone's at all interested, this works:

MemNoRow = WorksheetFunction.Match(FLAGFileNo, Workbooks(TalkRegTempFile).Worksheets("Setup Sheet").Range("A1", "A" & CStr(LastMemRow)), 0)

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