Search column identified by VBA number
Basically I have a function that looks for a specific sheet for a column header and then returns the column number.
I also need another function that searches this column for a specified value. It would be great to combine the two, but I'm not sure how to express the search range using the column number.
Below is my code; there isn't much to say, i just don't know what to put in the range bit
Hooray!
Function getValue(...parameters...) As Double
col = getColumnNumber(worksheetName, columnTitle) 'get column number
With Worksheets(worksheetName).Range(****)
...conditions for which value
End With
End Function
source to share
Try
With Worksheets(worksheetName).Columns(col)
and you can access the cells using .Cells
just like any other range.
You can see the type of various expressions and variables with the watch / variable window in VBA, or with a function TypeName
like:
Debug.Print TypeName(Worksheets("Sheet1").Columns(1))
which returns Range
.
source to share
Use Worksheet(wsName).Columns(columnNumber)
to reference the target column.
Here is some sample code to solve the problem:
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
source to share