Results 1 to 10 of 10

Thread: Is there a better way to make my code less tedious?

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

    Default Is there a better way to make my code less tedious?

    Hi guys, I am making a datasheet for a power substation and I created a number pad that has a command button on it called "Next" what I want this button to do is every time it is clicked it will automatically go to a specific cell and then be filled out. As of right now I do have code that works but its clunky and if I want to have an upwards of 300 specified cells its going to be a pain to hard code it all in. So I am curious to see if there is a better way? Thanks in advanced for the help.

    Code:
    Public s As Integer
    Private Sub CommandButton15_Click()
       
    If s = 0 Then
            ThisWorkbook.Sheets("sheet1").Range("e1").Select
            s = 1
        ElseIf s = 1 Then
            ThisWorkbook.Sheets("sheet1").Range("b5").Select
            s = 2
        ElseIf s = 2 Then
            ThisWorkbook.Sheets("sheet1").Range("c5").Select
            s = 3
        ElseIf s = 3 Then
            ThisWorkbook.Sheets("sheet1").Range("d5").Select
            s = 4
        ElseIf s = 4 Then
            ThisWorkbook.Sheets("sheet1").Range("e5").Select
            s = 5
    Else
            
            s = 0
        End If
        
    End Sub

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,647
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Is there a better way to make my code less tedious?

    Before you start you cant really use this:

    Code:
    ThisWorkbook.Sheets("sheet1").Range("e1").Select
    Well you can but unless thisworkbook sheet1 is active it will error.

  3. #3
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,647
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Is there a better way to make my code less tedious?

    This does the same assuming Sheet1 is active:

    Code:
    Select Case s
        Case 0: Cells(1, 5).Select: s = 1
        Case 1 To 4: Cells(5, 1 + s).Select: s = s + 1
        Case Else: s = 0
    End Select

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,208
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Is there a better way to make my code less tedious?

    You can create a temporary sheet and put the relation of S and the cell, for example:

    Temp

     AB
    1SCELL
    21E1
    32B5
    43C5
    54D5
    65E5
    76F3
    87G6
    98H8
    10
    11
    12nXn


    The code would be like this
    Code:
    Public s As Integer
    Private Sub CommandButton15_Click()
        Dim sh As Worksheet
        Dim f As Range
        
        ThisWorkbook.Activate
        Sheets("Sheet1").Select
        Set sh = Sheets("Temp")
        Set f = sh.Range("A:A").Find(s, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            Range(f.Offset(0, 1)).Select
        Else
            s = 0
        End If
    End Sub
    Regards Dante Amor

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

    Default Re: Is there a better way to make my code less tedious?

    Another option
    Code:
    Private Sub CommandButton15_Click()
       Dim Ary As Variant, Ndx As Variant
       
       Ary = Array("E1", "B5", "C5", "D5", "E5", "B7", "D7", "E1")
       Ndx = Application.Match(ActiveCell.Address(0, 0), Ary, 0)
       If IsError(Ndx) Then Range(Ary(0)).Select Else Range(Ary(Ndx)).Select
    End Sub
    Last edited by Fluff; Jun 13th, 2019 at 09:18 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Is there a better way to make my code less tedious?

    Thank you both. Dante I am not good with vba so I was wondering if I follow the format you posted should it just work as is or are there changes I need to make in the code you provided? If there are changes could you describe them a little bit to me?

  7. #7
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,033
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a better way to make my code less tedious?

    Hi,
    untested but another idea maybe

    Code:
     ActiveSheet.Cells(IIf(s = 0, 1, 5), IIf(s = 0, 5, s + 1)).Select
        s = s + 1


    Dave
    Last edited by dmt32; Jun 13th, 2019 at 09:23 AM.

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

    Default Re: Is there a better way to make my code less tedious?

    Hi Fluff your code works great I really appreciate it! I'm just lazy but, is there a way to fill in the cells I want onto the Ary variable without just hand typing them in? Also, Is there a way to make this code loop? For example, once it goes through all the arrays it will start again at cell E1?

    Thanks for all the help,
    Nate

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,208
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Is there a better way to make my code less tedious?

    Quote Originally Posted by Naykar View Post
    Thank you both. Dante I am not good with vba so I was wondering if I follow the format you posted should it just work as is or are there changes I need to make in the code you provided? If there are changes could you describe them a little bit to me?

    There are no changes in the code, you just have to create the "Temp" sheet and fill columns A and B of the "Temp" sheet
    Regards Dante Amor

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

    Default Re: Is there a better way to make my code less tedious?

    Quote Originally Posted by Naykar View Post
    Hi Fluff your code works great I really appreciate it! I'm just lazy but, is there a way to fill in the cells I want onto the Ary variable without just hand typing them in? Also, Is there a way to make this code loop? For example, once it goes through all the arrays it will start again at cell E1?

    Thanks for all the help,
    Nate
    For the last part, it already does that as long as the start cell is both first & last value in the array.
    For the 1st part, unfortunately not. You could enter them into a sheet & refer to that, but you'd still need to type them into the sheet to begin with.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •