VBA Countif -Out of range

[Dean Zuki]

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

[jindon]

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

It needs to be open.

[Dean Zuki]

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

[jindon]

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

[Dean Zuki]

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

[jindon]

That means it is not opened in your pc.

Try something like

Please Login or Register  to view this content.

[Dean Zuki]

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

[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.

[Dean Zuki]

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

[jindon]

How is it not working?

Is Mid(TextBox2.Text, 1, 8) a number?

Please Login or Register  to view this content.

[Dean Zuki]

Yes its a number.

[Dean Zuki]

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

[jindon]

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

[Dean Zuki]

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

Please Login or Register  to view this content.

Thank you

[jindon]

And what do you mean by

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

[Dean Zuki]

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 ..

[jindon]

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

What for?

[Dean Zuki]

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

[jindon]

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.

[Dean Zuki]

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

[jindon]

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

You CAN NOT add data while it is open somewhere.

[Dean Zuki]

If I want to check for duplicate data only? Is it possible? Reason for open 2 file (2 computer) when the crowd to huge my pos printer will jammed....

[jindon]

Duplicate within Dean-Temp-PC2.xlsm?

I asked about it already...What for?

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

[Dean Zuki]

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

[jindon]

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.

[Dean Zuki]

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

[jindon]

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...

[Dean Zuki]

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.

[jindon]

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.

[Dean Zuki]

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

[jindon]

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.

[Dean Zuki]

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