VBA Countif -Out of range

Dear Expert.

I having an issue for Countif function. Below code showed out of range.

Please Login or Register  to view this content.

The file in was saved in same folder. Any idea?

Thank you

Is Workbooks("Dean-Temp-PC2.xlsm") open?

It needs to be open.

Yes its open... but the file is opened from other PC...

What do you mean "file is opened from other PC"?

I setting up 2 PC into single network use cross cable...

That means it is not opened in your pc.

Try something like

Please Login or Register  to view this content.

Its worked.... thank you very much Sir Jindon….

That's good and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Hi Jindon, sorry. now the code is not working. when I try again it will not check the other file. It should check the data at PC2 file at sheet "Datas" range A:A. Is there I miss something here.

Please Login or Register  to view this content.

Thank you

1 more thing, current only check for 1 data how to extend into range A:A column?

Show me your entire code and how you want the result.

Hi Jindon, please see below. The result should prevent duplicate data between 2 workbook.

Please Login or Register  to view this content.

Thank you

And what do you mean by

Originally Posted by Dean Zuki

1 more thing, current only check for 1 data how to extend into range A:A column?

Current code only able to check for 1st data only... After that it did not check for duplicate data from workbook 2...hope my explanation is clear ..

Do you mean you want to count all the matched data in [Dean-Temp-PC2.xlsm]Datas Col.A?

What for?

Previous I use countif as per below code to check if the data exist. If exist, MsgBox appear "This Employee Number already exists". If not, copy the data to sheets "Datas".

Please Login or Register  to view this content.

Thank you

Then you will need to open the workbook, Dean-Temp-PC2.xlsm in the same instance, and you also need to close it from other pc.

But both file need to run on the same time and cannot be close.... any advice...

Why do you need to open the other workbook in the other pc?

You CAN NOT add data while it is open somewhere.

Duplicate within Dean-Temp-PC2.xlsm?

I asked about it already...What for?

You should control it within Dean-Temp-PC2.xlsm.

To check Duplicate within Dean-Temp-PC2.xlsm & Dean-Temp-PC1.xlsm. Sorry ....

What do you mean by "duplicates" exactly?

Is it something to do with TextBox value, or duplicates against column to column?

Post 2 workbooks and show me what exactly you wish to happen, otherwise I don't understand what you are trying to do.

Hi Jindon, Yes, it is to do with Textbox value. Sorry for late reply. I having CNY Lion Dance ceremony. Please see attached file.

tq

Did you build all the codes attached to both files?

I still don't understand what you are trying to do about "duplicate".

Both files have no sheet called "Data", anyway...

No. I just google & modified to suit my need. Below original code from Holger who help me many years back.

Please Login or Register  to view this content.

Explain what do you want to happen exactly when you enter the number

1) that already exists in other workbook.
2) that is not in the other workbook.

In detail.

Here the scenario...
1. Employees will swipe their ID card at PC1
2. System will check whether he/she have swiped at PC2 (by checking their ID number at sheet "Datas")
3. If their ID number already exist, system will reject their swipe
4. IF not then the data will copy into sheet "datas" at file 1 (PC1)

Note: the same process applied for file 2 at PC2. Hope my explanation clear ....sample print screen.jpg

So, possibly multiple users would be entering the data at the same time.

In that case Excel is not the software that you should use, learn ACCESS or other database software.

Noted Sir Jindon. Thank you for your patience...