none
VBA - Application GoTo - Reference is not Valid RRS feed

  • Question


  • Hi friends,

     

    I have got stuck on a goto statement, and I have tried everything to get it it to work :(

     

    A nice person here helped me with this function so that I can go to  a cell location, on click of the active cell.

     

    I'm afraid I may have made a mistake - but I cant spot it.

     

     
    '  Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    '
    '
    '   GoToCellLocation Target
    '
    '  End Sub
    '
     
     
     Function GoToCellLocation(selectedCell As Range)
    
       ' Vish
       
        Dim iRow As Integer
        iRow = 2 ' Data Starts  Row 2
           While Range("A" & iRow).Value <> ""
               If Not Intersect(selectedCell, Range(Range("A" & iRow).Value)) Is Nothing Then
    
            '- List Cells in Column A  - Column B - Go To Cell Location
    
            'Application.Goto Range("A" & iRow).Offset(0, 1).Value
    
             Application.Goto ActiveSheet.Range("A" & iRow).Offset(0, 1).Value
             
             'Application.Goto Worksheets("Data").Range("A" & iRow).Offset(0, 1).Value
                    
             'Application.Goto Reference:=Worksheets("Data").Range("A" & iRow).Offset(0, 1).Value
                    
            
                   Exit Function
               End If
               iRow = iRow + 1
           Wend
    End Function

     

     

    I will refrain from trying to explain this as I'm sure I will explain it wrong, can a pro expert advise why the application goto statement errors, it works in another situation but not when I put Cell addresses in column B.

     

    Thank you

     



    Cheers Dan :)


    Saturday, December 3, 2016 1:09 AM

Answers

  • Hi Dan,

    Here in this case there are two possibilities :

    Lets say in Column You have a cell address AZ1 and in corresponding column B value is BZ1. Story does not end here! In column A, there is another cell somewhere having value as BZ1 and corresponding B column Value is CZ1

    In above case, you might accept following two:

    1. Whatever first selection happening based on Column A and B values, it should stop there i.e. control should have selected the cell BZ1 and NOT CZ1

    2. It is possible that you want again the whole process to be repeated and control to go and select CZ1 and remain there on BZ1

    Following is the code for your both situations:

    Situation 1:

    Function GoToCellLocation(selectedCell As Range)
        Dim iRow As Integer
        On Error GoTo err
        iRow = 2 ' Data Starts  Row 2
           While Range("A" & iRow).Value <> ""
               If Not Intersect(selectedCell, Range(Range("A" & iRow).Value)) Is Nothing Then
                '- List Cells in Column A  - Column B - Go To Cell Location
                  'Application.EnableEvents = False
                  Application.Goto Reference:=Worksheets("Data").Range(Range("A" & iRow).Offset(0, 1).Value)
                  Exit Function
               End If
            iRow = iRow + 1
           Wend
    err:
           Application.EnableEvents = True
    End Function
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    GoToCellLocation Target
    End Sub
    

    Situation 2:

    Function GoToCellLocation(selectedCell As Range)
        Dim iRow As Integer
        On Error GoTo err
        iRow = 2 ' Data Starts  Row 2
           While Range("A" & iRow).Value <> ""
               If Not Intersect(selectedCell, Range(Range("A" & iRow).Value)) Is Nothing Then
                '- List Cells in Column A  - Column B - Go To Cell Location
                  Application.EnableEvents = False
                  Application.Goto Reference:=Worksheets("Data").Range(Range("A" & iRow).Offset(0, 1).Value)
                  Exit Function
               End If
            iRow = iRow + 1
           Wend
    err:
           Application.EnableEvents = True
    End Function
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    GoToCellLocation Target
    End Sub
    


    Vish Mishra

    • Marked as answer by Dan_CS Saturday, December 3, 2016 11:18 AM
    Saturday, December 3, 2016 4:50 AM

All replies

  • Hi Dan_CS,

    I've made a sample VAB code:
      when click a cell B2, B3..., a cell described on B2, B3... will be selected.
      
      
    ' -- Copy the below & Paste onto worksheet "Data" in your VBAProject  
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Intersect(Target, Range("B2:B100")) Is Nothing Then
            Exit Sub
        Else
            ' --- Go to Location
            If (ActiveCell.Value <> "") Then
                Dim cellValue As String
                cellValue = ActiveCell.Value
                Range(cellValue).Select
            End If
        End If
    End Sub

    Regards,
    Ashidacchi
    Saturday, December 3, 2016 3:34 AM
  • Hi Dan,

    Here in this case there are two possibilities :

    Lets say in Column You have a cell address AZ1 and in corresponding column B value is BZ1. Story does not end here! In column A, there is another cell somewhere having value as BZ1 and corresponding B column Value is CZ1

    In above case, you might accept following two:

    1. Whatever first selection happening based on Column A and B values, it should stop there i.e. control should have selected the cell BZ1 and NOT CZ1

    2. It is possible that you want again the whole process to be repeated and control to go and select CZ1 and remain there on BZ1

    Following is the code for your both situations:

    Situation 1:

    Function GoToCellLocation(selectedCell As Range)
        Dim iRow As Integer
        On Error GoTo err
        iRow = 2 ' Data Starts  Row 2
           While Range("A" & iRow).Value <> ""
               If Not Intersect(selectedCell, Range(Range("A" & iRow).Value)) Is Nothing Then
                '- List Cells in Column A  - Column B - Go To Cell Location
                  'Application.EnableEvents = False
                  Application.Goto Reference:=Worksheets("Data").Range(Range("A" & iRow).Offset(0, 1).Value)
                  Exit Function
               End If
            iRow = iRow + 1
           Wend
    err:
           Application.EnableEvents = True
    End Function
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    GoToCellLocation Target
    End Sub
    

    Situation 2:

    Function GoToCellLocation(selectedCell As Range)
        Dim iRow As Integer
        On Error GoTo err
        iRow = 2 ' Data Starts  Row 2
           While Range("A" & iRow).Value <> ""
               If Not Intersect(selectedCell, Range(Range("A" & iRow).Value)) Is Nothing Then
                '- List Cells in Column A  - Column B - Go To Cell Location
                  Application.EnableEvents = False
                  Application.Goto Reference:=Worksheets("Data").Range(Range("A" & iRow).Offset(0, 1).Value)
                  Exit Function
               End If
            iRow = iRow + 1
           Wend
    err:
           Application.EnableEvents = True
    End Function
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    GoToCellLocation Target
    End Sub
    


    Vish Mishra

    • Marked as answer by Dan_CS Saturday, December 3, 2016 11:18 AM
    Saturday, December 3, 2016 4:50 AM
  • Hello my friends,

    Thank you both for the code and the added insights.

     

    I was very annoyed yesterday as I had 5 versions of my code,but it just would not budge  - vba is very stubborn!

     

    This is the first time I have come across a double range - Range within a range.

     

     Application.Goto Reference:=Worksheets("Data").Range(Range("A" & iRow).Offset(0, 1).Value)

     

    Or I most probably have come across it before but I never paid any attention to it :)

     

    Thank you very much for helping me to understand the syntax, I can do lots of new things now by adapting my code yay!

    .... but first I will need a cup of tea.

    Have a wonderful weekend now!

    🙂


    Cheers Dan :)



    • Edited by Dan_CS Saturday, December 3, 2016 11:28 AM
    Saturday, December 3, 2016 11:17 AM