Searching column defined by number VBA

Question

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

Rusan Kax · Accepted Answer · 2015-01-09 11:35:13Z

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.

silentsurfer · Accepted Answer · 2015-01-09 12:51:43Z

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

Collectives™ on Stack Overflow

Searching column defined by number VBA

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related