1

I've made a spreadsheet for a Q&A section I'm working on and have macros set to go to question 1 when the button question 1 is clicked. The macro for question one is searching for a key work, in this case, Q1 - The code I'll paste below is working as long as no additional cells are inserted.

Here is the code I'm using:

Sub Question01()
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("A30:A10000")
For Each myCell In myRange
If myCell Like "Q1" Or _
myCell Like "Q1" Then

Range("A75").Select
ActiveWindow.ScrollRow = ActiveCell.Row


End If
Next myCell

End Sub

I think the issue is with the range("A75").Select as if everything is left alone it works, but when a blank row is added above A75 the key work Q1 is no longer at the top of the spreadsheet, but is one down from where it is supposed to be.

Thanks in advance for any assistance!

Danny

Ben Rhys-Lewis
  • 3,118
  • 8
  • 34
  • 45
Danny Brown
  • 13
  • 1
  • 4
  • Why not use the `Find` instead of looping all of 10,000 rows looking for the value? Also, I think you can use `Application.GoTo` instead of `ScrollRow` – David Zemens Feb 18 '16 at 18:00
  • 1
    @Ralph how about we try to avoid relying on `Select` and `Activate` whenever possible :) http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – David Zemens Feb 18 '16 at 18:04

1 Answers1

1

I think the issue is with the range("A75").Select

Yes, and this is because Range("A75").Select makes A75 the ActiveCell, so the following statement will scroll to... A75 (the active cell).

ActiveWindow.ScrollRow = ActiveCell.Row

I would probably use the Find method instead of brute force iteration:

Sub Question01()
    Dim myRange As Range
    Dim myCell As Range
    Set myRange = Range("A30:A10000")

    Set myCell = myRange.Find("Q1", myRange.Cells(1), LookAt:=xlWhole)

    If Not myCell Is Nothing Then

        ActiveWindow.ScrollRow = myCell.Row
        ' and/or:
        Application.GoTo myCell


    Else
        ' The value is not found in the range, so inform you:
        MsgBox "Not found!"
    End If
End Sub

Alternatively, use the Match function:

Sub Question01()
    Dim myRange As Range
    Dim myRow As Variant                '### NOTE THIS CHANGE!
    Set myRange = Range("A30:A10000")

    myRow = Application.Match("Q1", myRange, False)

    If Not IsError(myRow) Then
        ActiveWindow.ScrollRow = myRow
        ' and then select/activate the cell:
        Application.GoTo Cells(1, myRow)
    Else
        ' The value is not found in the range, so inform you:
        MsgBox "Not found!"
    End If
End Sub
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • 1
    Thank you so much! Works like a charm, but is there a way to have the cell with the keyword Q1 position itself at the very top left of the spreadsheet? – Danny Brown Feb 18 '16 at 18:23
  • Ahhh OK. yes, you may need to use the `ScrollRow` method for that. I thought it sufficient to simply bring the cell in to view on the screen, but see revision to my answer above. – David Zemens Feb 18 '16 at 18:27
  • Excellent David! Works perfectly! I'm in awe! I'm trying to make this as error free (user proof) as I can and before your magical code everything was good until some either accidently or on purpose added a cell. Do this enough times and it could really throw things off! – Danny Brown Feb 18 '16 at 18:37
  • Sorry to post again as you have already helped me so much, but I deleted Q1 so I could see what the error box looked like, but instead of an error box it went to cell A10. I'm guessing it's simply seeing the 1 and then going to the first it sees. Any ideas? – Danny Brown Feb 18 '16 at 18:42
  • It's not looking for a "1"... what value is in A10? – David Zemens Feb 18 '16 at 18:45
  • (I can send you the spreadsheet if it helps) I've got 30 questions with 75 blank cells in-between each Q1, Q2, Q3....Q30 to give managers room to actually enter the question/answers. On line A75 if I have the text Q1 in the cell and your code works well, but if I remove the Q1 the code skips all single digit numbers and lands of Q10 with is cell A750. – Danny Brown Feb 18 '16 at 18:53
  • Sorry A10 was a typo - should of been Q10. – Danny Brown Feb 18 '16 at 18:53
  • OK, so by default, `Find` will search for a partial match. I've revised so that it should only look at a complete match, i.e., "Q10" will not return false positive for "Q1". See if that solves it. – David Zemens Feb 18 '16 at 22:04
  • David it works great! Thank you so very much. If I might impose upon you just one last time, could you enter the code to where the top left cell is selected? – Danny Brown Feb 19 '16 at 17:42
  • What do you mean by "top left cell"? Top left, within what range or row? – David Zemens Feb 19 '16 at 17:43
  • Say for example the user hits the Q1 button. Spreadsheet goes to cell 75, your code puts cell 75 at the top of excel now it just needs to be selected. The reason for this is because as it is now if you hit the arrows to start scrolling through question 1 it goes back up to the top. Thanks again! – Danny Brown Feb 19 '16 at 17:44
  • You probably want to just uncomment the line `Application.GoTo Cells(1, myRow)` – David Zemens Feb 19 '16 at 17:44