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

Naykar

New Member
Joined
Jun 13, 2019
Messages
3
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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
 
Upvote 0
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
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
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top