part of macro not running

[rjplante]

I have created a macro to scroll to the row that begins with the letter selected. I had this working great before lunch. I copied the macro and the activeX field over to another workbook. Now the part where it scroll to the selected letter row does not work. If the search cell is blank, it will scroll to the home row just fine. What is going on? Why won't the GoRow portion work any more?

Thanks for the help.

Robert

    Dim GoRow As Long
    Dim HomeRow As Long
    
    Application.ScreenUpdating = False
    Sheets("All Agreements").Unprotect


    GoRow = ActiveWorkbook.Sheets("All Agreements").Range("E8:E1000000").Find(Sheets("All Agreements").Range("AJ1").Value & "*", , , , , , False).Row
    HomeRow = Sheets("All Agreements").Range("E8").Row
    
'   Scroll to row of selected letter
    If Sheets("All Agreements").Range("AJ1").Value = "" Then
        With ActiveWindow
            .ScrollRow = HomeRow
        End With
    Else
        With ActiveWindow
            .ScrollRow = GoRow
        End With
    End If


    With Sheet1
        .ComboBox2.ListIndex = -1
    End With

 

[rjplante]

Correction: Now is seems to go to the cell where the selected letter first appears, Which is usually somewhere in the A's. Still confused.

 

[JoeMo]

Possibly the find target is not found. Here's a modification to your code to replace the GoRow line that will help to diagnose the problem:

Dim GoCell As Range
Set GoCell = ActiveWorkbook.Sheets("All Agreements").Range("E8:E1000000").Find(Sheets("All Agreements").Range("AJ1").Value & "*", _
    , , , , , False)
If GoCell Is Nothing Then
    MsgBox "Not found"
Else
    GoRow = GoCell.Row
End If
'rest of code

 

[rjplante]

Thanks for the suggestion. I implemented your diagnostic fix, to no avail. It will still just go the first row where my selected letter appears. Still in the A section. A mystery even more confusing.

 

[JoeMo]

Thanks for the suggestion. I implemented your diagnostic fix, to no avail. It will still just go the first row where my selected letter appears. Still in the A section. A mystery even more confusing.

I have no idea what you are trying to do or what the selected letter is all about so afraid I can't provide anything more.

 

[rjplante]

I have an ActiveX comboBox than has a drop down menu of the alphabet. This is linked to a cell in my spreadsheet. I spreadsheet has a column of company names. I would like to select a letter from the menu, click on a button and have the first row of the company name that begins with that letter be brought to the top. So if I select the letter L and click the button, the row with Larson Companies will appear in the first row below row 7, which is frozen. This is just supposed to be a quick navigation based on the letter that starts the text in Column E.