I have a macro that finds today on a spreadsheet and then I want to search down column for a value, if the value is found I want go to last cell xlleft and hold both values in an array. I have two macros, one to find today and set that as the starting point, I have found another macro to hold the data found. I need help in assigning the value found from today macro into the array macro, below are the macros I have, ie: the address of the date code should be the address the second marco searches from (n), in the code I have(n) = "I2". if possible could this be one macro?
Sub FindDate()
Dim R As Range
Dim rng As Range
For Each cell In ActiveSheet.Range("A1:IV1")
if cell.Value = [Today()] Then
Set R = cell
End If
Next
Set rng = Range(R, R.Offset(29, 0)
MsgBox R.Address
End Sub
Sub FindNames()
Dim strNames() As String
Dim n As Integer
n = Range("I2", Range("I2").End(xlDown)).Rows.Count
ReDim strNames(n)
Dim i As Integer
For i = 0 To n
f Range("I2").Offset(i, 0).Value = "HA (F)" Or _
Range("I2").Offset(i, 0).Value = "HA (H)" Then
strNames(i) = Range("I2").Offset(i, -8).Value & " " & _
Range("I2").Offset(i, 0).Value
End If
Next i
MsgBox Join(strNames, vbCrLf)
End Sub
Screenshot