CODEWITHSUNDEEP
excelvbavariables
Richard154
Richard154

Reputation: 1

How to populate a variable using a formula

The basic idea is to have a search, that will not only highlight a row which contains a specific value in column C, but also takes me there. I'm trying to combine the conditional formatting, with the CTRL+F function, basically.

My spreadsheet is about 6000 rows.

I've tried various apporoaches, but the closest I've managed to come is using this:

=MATCH($U$1, $C2:$C6000,0)

To get me the row where the text in that row matches the value I've typed in my "Search box" located in cell U1. This cell is part of my frozen title bar, so it's always available on the screen.

This will return the row from my search range, let's say it's 1175. Since I have a frozen title row, my target row will end up being Row 1176 in the actual spreadsheet. My aim is to have Excel go to row 1176, highlight that row, and select it as the active row, so that I can immediately work on that row.

I use this formula to generate the actual cell reference:

=CONCATENATE("C",MATCH($U$1, $C2:$C6000,0)+1)

My problem is that I can't find a way to get Excel to just jump to row 1176, based on the result of the above forumla. So my solution was a short sub in VBA:

Sub Go_To()
    Dim Target_Cell As String
    Target_Cell = CONCATENATE("C",MATCH($U$1, $C2:$C6000,0)+1)
    Range(Target_Cell).Activate
End Sub

This doesn't work, and I can't figure out why. The variable doesn't seem to poplulate with C1176. The forumal works fine and displays the correct cell reference when I use it in Excel, but it doesn't work in VBA.

Can anyone shed some light on why it's failing there?

Upvotes: 0

Views: 32

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You need to use the WorksheetFunction.Match method in the proper way. And strings are concatenated with & in VBA:

Option Explicit

Public Sub JumpTo()
    Dim MatchedRow As Double
    On Error Resume Next 'next line throws error if no match is found
    MatchedRow = Application.WorksheetFunction.Match(Range("U1"), Range("C2:C6000"), 0)
    On Error Goto 0 'always re-activate error reporting

    If MatchedRow <> 0 Then
        Range("C" & MatchedRow + 1).Select
    Else
        MsgBox "'" & Range("U1") & "' was not found."
    End If
End Sub

Upvotes: 0

Related Questions