Results 1 to 7 of 7

Thread: Help with VBA coding

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with VBA coding

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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Help with VBA coding

    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)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA coding

    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.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Help with VBA coding

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA coding

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

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA coding

    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.

  7. #7
    Board Regular
    Join Date
    Mar 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA coding

    Quote Originally Posted by MissingInAction View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •