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
Sheet Module e.g. Sheet1(Cost for Claims)
Cost for Claims
in parentheses and copy the following code into the window that opened.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