Selecting rows from a table


New Member
Oct 27, 2016
Hi everyone,

I couldn't find anything in the forum that could answer my question so here goes nothing...

I am recreating a table from an older board game that uses a slide rule to report numbers. During the game, the slide moves up and down according to what space you land on. However, if you hit the top of the table (e.g. You are at row #2 and the space says move Up 4), you move up 1 and then proceed to move back down 3 to fulfill the remaining spaces. I can code this by hand, but I really don't want to see how ugly the code gets when it's Move Up 20. Here's what I have so far:

ListBox1 contains the Values 1-5, and 20.
cmdUp is a toggle button (I also have a toggle button for down)
B20 contains what row number of the table the current selection is at.
B16 is what the prior value of the stock that needs to be changed. (The other stocks are coded to use VLookup to determine their values.)

Private Sub CmdOk2_Click()

Dim x As Integer

x = Range("B20").Value

Sheets("Stock Values").Select

    If cmdUp.Value = True Then
        If ListBox1.Value = 1 Then
            If x <= "1" Then
               ActiveCell.Offset(-1, 0).Select
                Sheets("Game!").Range("B16").Value = Sheets("Stock Values").ActiveCell.Value
            End If
            ActiveCell.Offset(1, 0).Select
            Sheets("Game!").Range("B16").Value = Sheets("Stock Values").ActiveCell.Value
        End If
        If ListBox1.Value = 2 Then
            If x = "1" Then
                ActiveCell.Offset(-2, 0).Select
                Sheets("Game!").Range("B16").Value = Sheets("Stock Values").ActiveCell.Value
            End If
            ElseIf x = "2" Then
                'This selection moves the table up one, and then down one, virtually doing nothing.
                End If
                ActiveCell.Offset(2, 0).Select
                Sheets("Game!").Range("B16").Value = Sheets("Stock Values").ActiveCell.Value
         End if
End Sub
I tried to think of an Algebraic way of approaching this, but I couldn't find something that would work universally.

Sorry for the noob post. And the game is "Stock Market" if that helps any.

Thank you in advance for any help.


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...