Please shorten this code for me

  • I've done this code the long way and would like for it to be shortened.



    Essentially, just need to use loops to get what I'm trying to achieve, but can't work it out :nono:


  • Re: Please shorten this code for me


    Try this:

  • Re: Please shorten this code for me


    Amazing mate. Thank you so much! I'll be able to use this code for future projects so I really appreciate it.

  • Re: Please shorten this code for me


    Can I just ask skywriter, for some reason it seems to be adding +1 to the date cell, which shouldn't be affected. I have no idea why itd be doing this!


    Basically theres a date in cell B2, that it keeps adding 1 to, I have no idea why as there is no value in the cell to the left of it and also no matching value in the comboboxes!! What the!?

  • Re: Please shorten this code for me


    Okay so, for each combobox it's looking for the value that was in the combobox and when it finds that it adds 1 to the value that it found, which would be the same as what was in the combobox and puts that value into the cell 1 column to the right on the same row.


    Does that make sense and is that what you want?

  • Re: Please shorten this code for me


    Close. What it does it, it finds a name. Example lets say you select skywriter in the combo box. Then it will locate skywriter in range A:A and then put a +1 in the column to the right of your name. Which is why you/I have used the offset(0,1).


    That's all working fine. But for some reason, in range A theres a blank cell above all the names and to the right of that blank cell there is a date (e.g 16/03/2016). And whenever I hit submit, it will add 1 to that date - No idea whY!

  • Re: Please shorten this code for me


    Sorry I should have clarified in my last post - The code still works perfectly. Does exactly what I need it to do! Works perfectly.


    Its just whilst it also does what its meant to do, its adding +1 to this random date cell for some reason, I literally can see nothing that would affect this!

  • Re: Please shorten this code for me


    If the cell in column A next to where it's adding 1 is empty, the cause could be that one of your text boxes is empty?


    Therefore it's looking for nothing and when it finds it, then it adds 1 to the cell next to it?


    This would possibly fix that.

    Code
    Private Sub CommandButton1_Click()
        Dim y As Long, rngFound As Range
        For y = 1 To 27
            If Me.Controls("ComboBox" & y).Value <> "" Then
                Set rngFound = Sheets("Sheet1").Columns("A").Find(what:=Me.Controls("ComboBox" & y).Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not rngFound Is Nothing Then rngFound.Offset(, 1).Value = rngFound.Value + 1
            End If
        Next y
    End Sub
  • Re: Please shorten this code for me


    I did think that, but then it made me think, if that was the case - Wouldn't it be adding 1 next to EVERY blank combo box? Which it doesn't do

  • Re: Please shorten this code for me


    In any case, that new code you just wrote FIXES that date changing issue!!! Last question since you've been so helpful, not a big one but just curious. Whenever I load the userform, it takes a good 10-15 seconds to load and freezes excel whilst it does this, I'm assuming because it needs to load the date into all the comboboxes and being 27 of them it may be a slow process. Is there anything that could speed this up? Or just how it is? Thanks again for all your help

  • Re: Please shorten this code for me


    Quote from jbalth;767461

    I did think that, but then it made me think, if that was the case - Wouldn't it be adding 1 next to EVERY blank combo box? Which it doesn't do


    No it stops looking when it finds the first blank cell, and that same cell will always be the first one it finds.

  • Re: Please shorten this code for me


    Can you upload the workbook so I have something to work with?


    After you click reply, then there will be a new button where reply was that says "Go Advanced", click that button then there will be a paperclip icon on the toolbar that wasn't there before. Click that icon and it's pretty much self explanatory from there.


    Desensitize your data if you have to, if you have a lot of rows, you can delete most of them and leaving 10 or so should be good.

  • Re: Please shorten this code for me


    This is really fast. :cool:

  • Re: Please shorten this code for me


    First I put the values in an array, which you can read faster than you can the individual cells.


    Then I assigned the whole array to the combo boxes using .list instead of looping and using .add, it's very fast.

  • Re: Please shorten this code for me


    You should change your other code.
    Activating cells is unnecessary and slows your code down.

    Code
    Private Sub CommandButton1_Click()
        Dim y As Long, rngFound As Range
        For y = 1 To 27
            If Me.Controls("ComboBox" & y).Value <> "" Then
                Set rngFound = Sheets("Sheet1").Columns("A").Find(what:=Me.Controls("ComboBox" & y).Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not rngFound Is Nothing Then rngFound.Offset(, 1) = rngFound.Offset(, 1).Value + 1
            End If
        Next y
    End Sub


    You could also have an input box pop up asking you how many columns you want to offset and you could update columns farther to the right by just putting a variable in the code where the offset is.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!