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

[Naykar]

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.

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

 

[steve the fish]

Before you start you cant really use this:

ThisWorkbook.Sheets("sheet1").Range("e1").Select

Well you can but unless thisworkbook sheet1 is active it will error.

 

[steve the fish]

This does the same assuming Sheet1 is active:

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

 

[DanteAmor]

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

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Temp</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">S</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">CELL</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td style="text-align:center; ">E1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td style="text-align:center; ">B5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td style="text-align:center; ">C5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td style="text-align:center; ">D5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">5</td><td style="text-align:center; ">E5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">6</td><td style="text-align:center; ">F3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">7</td><td style="text-align:center; ">G6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">8</td><td style="text-align:center; ">H8</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">…</td><td style="text-align:right; ">…</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">…</td><td style="text-align:right; ">…</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">n</td><td style="text-align:center; ">Xn</td></tr></table>

The code would be like this

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

 

[Fluff]

Another option

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

 

[Naykar]

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?

 

[dmt32]

Hi,
untested but another idea maybe

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

Dave

 

[Naykar]

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

 

[DanteAmor]

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

 

[Fluff]

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.