excelvbaselectconditional-statementscell

Move the cursor to a cell in the Excel sheet according to the cell value in another Excel sheet


I need to move the cursor to a cell in column "F" in Sheet ("Claims data") after clicking on a cell in column "G" in Sheet ("Costs for claims") based on a value (e.g. 275_2018).

So when I click (double click or use enter) on a cell in the "G" column in the Sheet ("Costs for claims") it looks up and then moves the cursor to the second excel Sheet ("Claims data") to the cell in the "F" column.

The values in both sheets in columns F and G have the same format XXX_YYYY.

I tried this:

Sub moveToCell()
    Dim value As String

    value = Sheets("Costs for claims").Range("G3:G3000").Value

    Sheets("Voices data").Activate
    Sheets("Voices data").Range("F6:F3000").Select
End Sub

Solution

  • A Worksheet SelectionChange: Select Cell on Another Worksheet

    Sheet Module e.g. Sheet1(Cost for Claims)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        
        Const SRC_FIRST_CELL As String = "G3"
        Const SRC_ALLOW_MULTIPLE_CELLS_SELECTED As Boolean = True
        Const DST_SHEET_NAME As String = "Voices Data"
        Const DST_FIRST_CELL As String = "F6"
        Const DST_DO_SCROLL As Boolean = False
        
        Dim srg As Range, srCount As Long
        
        With Me.Range(SRC_FIRST_CELL)
            srCount = Me.Cells(Me.Rows.Count, .Column).End(xlUp).Row - .Row + 1
            If srCount < 1 Then Exit Sub ' no data
            Set srg = .Resize(srCount)
        End With
            
        Dim sirg As Range: Set sirg = Intersect(srg, Target)
        If sirg Is Nothing Then Exit Sub ' not source column
        If Not SRC_ALLOW_MULTIPLE_CELLS_SELECTED Then
            If sirg.Cells.CountLarge > 1 Then Exit Sub
        Endif
    
        Dim sStr As String: sStr = CStr(sirg.Cells(1).value)
        
        Dim dws As Worksheet: Set dws = Me.Parent.Sheets(DST_SHEET_NAME)
            
        Dim drg As Range, drCount As Long
        
        With dws.Range(DST_FIRST_CELL)
            drCount = dws.Cells(dws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
            If drCount < 1 Then Exit Sub ' no data
            Set drg = .Resize(drCount)
        End With
        
        Dim drIndex As Variant: drIndex = Application.Match(sStr, drg, 0)
        If IsError(drIndex) Then Exit Sub ' no match
        
        Application.Goto drg.Cells(drIndex), DST_DO_SCROLL
    
    End Sub