VBA: Find value "Year" and select cell on Activecell.Row

Question

	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027
1
2
3
4
5																		Value to find
6							Cell to select											2018
7
8
9
10

Answer 1

Hi Andre

Could you give us more details about what exactly you want to happen when running the macro?

Regards

Jeovany

Answer 2

ActiveCell is "S8" and i like to find that year in "B2:Q2" then when i found it, select the cell "H8" on the same as Activecell.Row

This is not static, So i cant just use Offset.

Answer 3

Hi André Jacobsson,

Thanks for your post in Microsoft Community.

I have summarized your requirements: to obtain the value of the currently selected cell and its row, find a matching value in the range B2 to Q2 based on the cell's value, get the column number of this match, and select the intersecting cell based on the column and row number. 

If I am mistaken, please let me know. 

If my understanding is correct, you can try the following macro: 

Sub FindAndSelectMatchingCell()

    Dim currentCell As Range

    Dim matchingCell As Range

    Dim colIndex As Long

    Dim rowNumber As Long

    Set currentCell = ActiveCell

    rowNumber = currentCell.Row

    Set matchingCell = Nothing

    For Each cell In Range("B2:Q2")

        If cell.Value = currentCell.Value Then

            Set matchingCell = cell

            Exit For

        End If

    Next cell

    If Not matchingCell Is Nothing Then

        colIndex = matchingCell.Column

        Dim targetCellAddress As String

        targetCellAddress = matchingCell.Parent.Cells(rowNumber, colIndex).Address

        Set matchingCell = matchingCell.Parent.Range(targetCellAddress)

        matchingCell.Select

    Else

        MsgBox "Value " & currentCell.Value & " was not found in range B2:Q2.", vbExclamation

    End If

End Sub

Please ensure that you have a cell selected, and the VBA program will automatically help you jump to the corresponding cell.

Before:

After:

I hope the information above is helpful to you, and I look forward to your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 4

Hi Andre

Here two options

Option 1) Exactly as requested

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim foundYear As Range

If Not Application.Intersect(Target, Range("S:S")) Is Nothing Then

    Set foundYear = Range("B2:Q2").Find(Target.Value, LookAt:=xlWhole, MatchCase:=True) 

    If foundYear Is Nothing Then 

        Exit Sub 

    Else 

        Cells(Target.Row, foundYear.Column).Select 

    End If 

End If

End Sub

Option 2) Enter the Year in a fixed cell and once found select the last empty cell in the found year column

This is the Option2 code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim foundYear As Range

If Not Application.Intersect(Target, Range("S3")) Is Nothing Then

    Set foundYear = Range("B2:Q2").Find(Target.Value, LookAt:=xlWhole, MatchCase:=True) 

    If foundYear Is Nothing Then 

        Exit Sub 

    Else 

        Cells(Rows.Count, foundYear.Column).End(xlUp).Offset(1).Select 

    End If 

End If

End Sub

IMPORTANT:

These codes are not for regular VBA modules, they go on the Sheet Change event panel

I hope this helps you

Regards

Jeovany

Answer 5

By the Way

You may download the sample file and codes here for testing. https://we.tl/t-W6HEv7wS2H

Jeovany