CODEWITHSUNDEEP
excelvba
Starec
Starec

Reputation: 15

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

Upvotes: 1

Views: 165

Answers (2)

Black cat
Black cat

Reputation: 6271

  • Right click the sheet tab of "Costs of Claim" and select View Code
  • Copy the below Sub in the opened window.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set second_sheet = Worksheets("Claims Data")

If Not Intersect(Columns(Target.Column), Columns("G")) Is Nothing And Target.Value Like "###_####" Then
  search_value = Target.Value
  Set find_cell = second_sheet.Columns("F").Find(search_value, , xlFormulas, xlWhole)
  If Not find_cell Is Nothing Then
    find_cell.Parent.Select
    find_cell.Select
  Else
    MsgBox "No item found!", 32
  End If

End If

End Sub

If the cells in column F contains excel or UDF functions then change xlFormulas to xlValues in the Find method. xlFormulas is faster then xlValues but works only with literal data.

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54853

A Worksheet SelectionChange: Select Cell on Another Worksheet

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

  • In the Project Explorer, double-click on the sheet with 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

Upvotes: 1

Related Questions