CODEWITHSUNDEEP
excelvbasearchrange
monarch
monarch

Reputation: 53

Searching column defined by number VBA

Basically I have a function which searches a certain worksheet for the title of a column, and then returns the column number.

I also need another function which searches this column for a specified value. It would be neat for me to combine the two, but I'm not sure how to express the range to search in using the column number.

An outline of my code is below; there's not really much to say, I just don't know what to put in the Range bit

Cheers!

Function getValue(...parameters...) As Double

col = getColumnNumber(worksheetName, columnTitle) 'get column number

With Worksheets(worksheetName).Range(****)

    ...conditions for which value

End With


End Function

Upvotes: 3

Views: 55

Answers (2)

silentsurfer
silentsurfer

Reputation: 2428

Use Worksheet(wsName).Columns(columnNumber) to reference your target column.

Here is a sample code of how the problem could be tackled:

Option Explicit

Function getColumnNumber(wsTarget As Worksheet, columnTitle As String) As Integer

    Dim rngHeader As Range
    Dim colNum As Integer
    Dim rngFind As Range

    Set rngHeader = wsTarget.Range("1:1")

    Set rngFind = rngHeader.Find(What:=columnTitle, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

    If Not rngFind Is Nothing Then
        getColumnNumber = rngFind.Column
    End If

End Function


Function valueExists(colName As String, findVal As String) As Boolean

    Dim colNum As Integer
    Dim wsTarget As Worksheet
    Dim rngFindVal As Range

    Set wsTarget = ThisWorkbook.Worksheets(1)
    colNum = getColumnNumber(wsTarget, colName)

    If colNum > 0 Then
        Set rngFindVal = wsTarget.Columns(colNum).Find(What:=findVal, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

        If Not rngFindVal Is Nothing Then
            valueExists = True
        Else
            valueExists = False
        End If
    Else
        MsgBox "Column header not found!", vbCritical
        valueExists = False
    End If

End Function

Sub test()
    MsgBox valueExists("myHeader", "myVal")
End Sub

Upvotes: 0

Rusan Kax
Rusan Kax

Reputation: 1894

Try With Worksheets(worksheetName).Columns(col) and you can access the cells using .Cells, like any other range.

You can see the type of various expressions and variables by using the watch/variable window in VBA, or by using TypeName function like so:

Debug.Print TypeName(Worksheets("Sheet1").Columns(1)), which returns Range.

Upvotes: 1

Related Questions