CODEWITHSUNDEEP
excelvba
ynitSed
ynitSed

Reputation: 145

Finding the column index for the first numeric or date column

I'm pretty new to VBA coding and I want to find the column index for the first numeric / short date / date column in a worksheet.

An example of what the data looks like this:

id     sex     2015   2016
2       M       1       3
4       F       7       5
3       F       8       9

In this example, the answer should be 3. I'd like to set this as a variable so I can use this number later.

This is my code so far:

Sub find_value()

Dim c As Range
Dim firstAddress As String

With Worksheets(1).Range("A1:A500") 
    Set c = .Find(2015, lookin:=xlValues) 

End Sub

As you can see, this is just a static solution.

Any ideas would be great! Thanks so much

Upvotes: 1

Views: 47

Answers (1)

Mech
Mech

Reputation: 4015

Very rough, but here is a function that would get you the result you are after. You can call on result for later use or call the function as you need. This is purposefully broken out so you can see how find_value becomes defined. You could amalgamate the code if you choose.

Sub test()
    Dim result As String
    result = find_value(2015, 1) 'find year 2015, entry 1
    
    Debug.Print result
    
End Sub

Function find_value(year As Integer, entry As Integer)
    Dim yearVal, entryVal, foundVal As Integer
    yearVal = Application.WorksheetFunction.Match(year, Worksheets(1).Range("1:1"), 0)
    entryVal = Application.WorksheetFunction.Match(entry, Worksheets(1).Range(Worksheets(1).Cells(1, yearVal), Worksheets(1).Cells(500, yearVal)), 0)
    foundVal = Cells(entryVal, yearVal + 1)
    find_value = foundVal
End Function

Upvotes: 1

Related Questions