CODEWITHSUNDEEP
excelvba
herrtodd33
herrtodd33

Reputation: 21

How to find cell value based on row and Column ID in excel VBA

Need a VBA Sub to find a cell value based on Row and Column ID. In the example below I need to select the value where East and RT3 intersect which is 80.

    A   B   C   D   E
1   null    RT1 RT2 RT3 RT4
2   North   31  40  78  11
3   South   32  41  79  12
4   East    33  42  80  13
5   West    34  43  81  14

Upvotes: 1

Views: 80337

Answers (3)

Son Goku ssj4
Son Goku ssj4

Reputation: 51

I know I'm late to the game but maybe for future people. I came up with this:

It takes a ListObject table in which it will find interesting cell and return it's string.

Returns ERROR when a) table name is not found, b) row or c) column name not found (it's up to you how you edit this)

Should take around: 0.0005s (5E-04s)

Public Function GetTableVal(ByVal tblName As String, ByVal rowName As String, ByVal colName As String) As String

    On Error Resume Next
        Dim rng As Range
        Set rng = Range(tblName)
    On Error GoTo 0
    
    If rng Is Nothing Then
        GetTableVal = "ERROR: Table not found"
        Exit Function
    End If
    
    Dim tbl As ListObject
    Set tbl = rng.ListObject
     
    On Error Resume Next
        Dim colIndex As Long
        colIndex = tbl.ListColumns(colName).INDEX
    On Error GoTo 0
        
    If colIndex = 0 Then
        GetTableVal = "ERROR: Column not found"
        Exit Function
    End If
    
    Dim rowIndexRange As Range
    Set rowIndexRange = tbl.ListColumns(1).Range.Find(rowName, LookIn:=xlValues, LookAt:=xlWhole)
    
    If rowIndexRange Is Nothing Then
        GetTableVal = "ERROR: Row not found"
        Exit Function
    End If
    
    Dim rowIndex As Long
    rowIndex = rowIndexRange.row - tbl.Range.row + 1
    
    Dim res As Range
    Set res = tbl.Range(rowIndex, colIndex)
     
    GetTableVal = res.value

End Function

Upvotes: 1

glh
glh

Reputation: 4972

Use similar to below not tested:

Function getcell(ct as string, rt as string) as range

    With activecell
        R = .columns("A").find(rt).row
        C = .rows("1").find(ct).Column
        'The below will escape the function if none where found
        If r = nothing or c = nothing then exit function
        Set getcell = .cells(r, c)
    End with

End function

Upvotes: 3

David
David

Reputation: 6571

There are different ways to do this, but one approach is to use a function with parameters. You didn't say how you intent to pass the parameters, so I just used a sub to call the function.

Function GetValue(row As Integer, col As Integer)
    GetValue = ActiveSheet.Cells(row, col)
End Function

Sub CallGetValue()
    Dim cellval As String
    cellval = GetValue(row:=4, col:=4)
    MsgBox (cellval)
End Sub

Upvotes: 1

Related Questions