Column Header Based on Date

[uk747]

Not sure how to find column headings in VBA when Dates are involved
How could I find based on a Cells Value or Even Todays Date in Col A:E

Also how could I find it in Columns G:J If Cells Value or Today was in the middle of the month
e.g. Cell Value is 12th Feb 2023. I would like to be able to Find it Answer would be H1 or Col8 (G1:J1 are 1st Days of month i.e. 1/1/23 1/2/23 1/3/23 etc)

A	B	C	D	E	F	G	H	I	J
01-Jan-23​	02-Jan-23​	03-Jan-23​	04-Jan-23​	05-Jan-23​		01-Jan​	01-Feb​	01-Mar​	01-Apr​

 

[GSP75]

To find a column based on a cell value or today's date in VBA, you can use a combination of the Range.Find method and the Range.Column property.

For example, to find a column based on a specific date in columns A:E, you could use the following code:
Dim searchDate As Date
searchDate = DateValue("12-Feb-2023")

Dim searchRange As Range
Set searchRange = Range("A:E")

Dim foundCell As Range
Set foundCell = searchRange.Find(searchDate, LookIn:=xlValues)

If Not foundCell Is Nothing Then
Dim foundColumn As Long
foundColumn = foundCell.Column

' use foundColumn for further processing
Else
' handle case where date is not found
End If

Similarly,
Dim todayDate As Date
todayDate = Date

Dim searchRange As Range
Set searchRange = Range("G:J")

Dim foundCell As Range
Set foundCell = searchRange.Find(todayDate, LookIn:=xlValues)

If Not foundCell Is Nothing Then
Dim foundColumn As Long
foundColumn = foundCell.Column

' use foundColumn for further processing
Else
' handle case where today's date is not found
End If

NoteLookIn parameterRange.Find method

 

[uk747]

The first one when looking for 12-Feb-2003 didn't find the column. The second one did find the column when i.entered today's date in a column header.

 

[GSP75]

Thank you for letting me know. In that case, it's possible that the search did not find the exact value of the date in the column header due to formatting differences. You can try using the Range.FindNext method to look for the next matching value in the range. Here's an example code that searches for a date in columns A:E and
Dim searchDate As Date
searchDate = DateValue("12-Feb-2023")

Dim searchRange As Range
Set searchRange = Range("A:E")

Dim foundCell As Range
Set foundCell = searchRange.Find(searchDate, LookIn:=xlValues)

If Not foundCell Is Nothing Then
Dim firstFound As Range
Set firstFound = foundCell
Do Until foundCell Is Nothing
Set foundCell = searchRange.FindNext(foundCell)
If foundCell.Address = firstFound.Address Then Exit Do
If foundCell.Column = firstFound.Column Then
Set foundCell = Nothing
Exit Do
End If
Loop
If Not foundCell Is Nothing Then
foundCell.Select
' use foundCell for further processing
Else
' handle case where date is not found
End If
Else
' handle case where date is not found
End If

This code searches for the first matching value of the date in the range, and then uses a loop to search for any additional matches. If there are multiple matches in the same column, the loop will continue until it reaches the first matching cell. If there are no additional matches, the loop will exit and the first matching cell will be selected.

 

[uk747]

Thanks. Don't have access to pc at moment will try next week. Maybe it was the formatting I entered but I thought the datevalue would have sorted that out

Does seem alot of code when using dates as when you are just searching for a regular text value you don't need much code

 

[GSP75]

Yes, working with dates in VBA can be a bit more complicated than working with regular text values, as you often need to convert them to the correct format before comparing or searching for them.

However, once you get the hang of it, it shouldn't be too difficult. And if you find yourself frequently searching for specific dates in your spreadsheet, it can be helpful to create a reusable function or subroutine to handle the search for you.

As for the amount of code needed to search for text values versus dates, it really depends on the complexity of your search criteria and what you need to do with the results. In some cases, searching for text values can be just as involved as searching for dates.