Help with VBA coding

[MissingInAction]

Good day everyone.
I'm looking for some help regarding a VBA script. I have an Excel database that a user must input data for a weighbridge/landfill facility. When a truck comes in, it is weighed, its data is captured and the truck can dump its cargo. When it leaves, it is weighed again and the difference is noted to know how many tons of cargo was dumped. This means the entry can only be sent to the database after the truck leaves. I have made provision for 10 entries to be used at the same time, since more than one truck can be on the facility before it leaves. Trucks also do not leave in the order they arrive. Once the truck has left and all data is filled in, the user can click a button that moves the data from the "Input" sheet to the "Data" sheet. Here is the code used:

<code>Sub Received_Entry_1()
' Copy data entry and go to Data sheet
    Range("A4:K4").Select
    Selection.Copy
    Sheets("Data").Select
    Range("A2").Select
' Select last empty row
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
' Paste entry
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
' Go to Input sheet and clear entry
    Sheets("Input").Select
    Range("B4:K4").Select
    Range("K4").Activate
    Selection.ClearContents
    Range("B4").Select
End Sub</code>

The macro is the same for the other 9 entries, the only difference is the range that is copied and cleared. You'll notice that I copy range A4:K4 but I only clear B4:K4. This is intended. Column A contains a date that I use for pivot table purposes and is linked to an data validation where the user can select the date range and must only change when its a new month.
The user must not be able to alter the entries after this code is executed, so I hide the cells in sheet "Data" and password protect the sheet. All of this works fine.
The problem comes where Column B range B4:B13 on the "Input" sheet contains a value that must never be duplicated in the entire Column B on the "Data" sheet. I have found plenty of CountIf formulas (example COUNTIF(Data!B:B;B2)=1) that can check for duplicates, but since my data is moved to another sheet and I only have 10 fields for entering data, it does not work that well.

My knowledge of VBA coding is next to nothing. I need help to modify the code above so that when a user enters a value in Column B (range B4:B13) on the "Input" sheet, that is already used in Column B on the "Data" sheet, he must get a message stating that the value is duplicated. Alternatively if there is a data validation formula I can use that will look for duplicates in the entire B Column on the "Data" sheet, using the values entered in range B4:B13 on the "Input" sheet, that would be great as well.

I hope someone can help me with this.
Thank you in advance

 

[Fluff]

Hi & welcome to MrExcel.
You could use this formula with data validation
=AND(COUNTIF(Data!$B:$B,B4)=0,COUNTIF($B$4:$B$13,B4)=1)

 

[MissingInAction]

Perfect, thank you Fluff.
Do you have any advise on how to troubleshoot the performance/speed on a workbook? I have tried this site from Microsoft, but it kept on saying the test took 0 seconds. I do have a sheet that has over 100 000 sumifs (literally), but this is needed unfortunately. As a test i reduced it to about 40k sumifs, but the speed still seems slow.
To clarify the workbook opens at a normal speed, but the calculation itself is very slow. I have an invoice sheet where the user can select the company via a drop down (data validation) list. Selecting a company there causes a calculation that takes about 2 minutes to complete.

 

[Fluff]

As I'm mainly VBA based, I've never had problems with slow worksheets, so can't really help.
Best bet is to search the net for something like "excel calculating slowly" and see what comes up.

 

[MissingInAction]

Thank you. I thought I was taking a chance asking that in the same thread.

 

[MissingInAction]

So I found the issue a while ago and thought to update this thread for future users that might have this problem. Having the LOOKUP formula search for a value in three entire columns, is not a good idea. I changed it to only search in the first 20k rows in each column and the calculation time went from 2 mins and 10 sec, to about 5 sec.

 

[Swayzy]

So I found the issue a while ago and thought to update this thread for future users that might have this problem. Having the LOOKUP formula search for a value in three entire columns, is not a good idea. I changed it to only search in the first 20k rows in each column and the calculation time went from 2 mins and 10 sec, to about 5 sec.

Yes, never reference more range than needed. The issue grows alot when you reference 1 000 000 rows 10 000 Times.

If you need an expandable range then try to use Excels formal tables instead