Search range for date, then capture details

**ks100** · 06-15-2015, 11:56 AM

I've done similar to this before but it's been a while so I'm kind of rusty.

I need to search a range (B2:ZZ500) for a date between today and variable X (X will be taken from a userform).

For each date in the range after today but before X, I need it to do the following:

Capture the column header value (so row 1 of whatever column it found the date in) as variable colheader

Capture the row header value (column A of whatever row the date was found in) as variable rowheader

(So if a matching date was found in B63, it would store the value in B1 as colheader and the value in A63 as rowheader.

At this point another script will run that will populate a new worksheet with colheader and rowheader, and then move back to the original spreadsheet to search for the next date in the range meeting this criteria, and repeat the process until all applicable dates have been found (and captures into the new worksheet).

I do not need help populating the new worksheet, I have the code for that part, I just need the rest of the script I described.

Thanks!

**JoeFoot** · 06-15-2015, 12:05 PM

try this:



Sub sort()

Dim i As Integer
Dim j As Integer
Dim colheader As Variant 'if its number or text just adjust as needed
Dim rowheader As Variant 'if its number or text just adjust as needed
Dim variable_X As Date

For i = 2 To 500

    For j = 2 To 702
                
            If Cells(i, j) > VBA.Date And Cells(, j) < variable_X Then 'taken from useform
                    
                    colheader = Cells(1, j)
                    rowheader = Cells(i, 1)
                    
                    'code to populate worksheet here before the loop continues the search
                    
                    
            
            End If
    
    Next j

Next i

End Sub

you might hit some snags with the date formats. Ensure that the date is properly formatted in the cells and coming out of your useform. I assumes variable_X is bigger than today's date. If not just adjust the instruction accordingly

cheers

**JOHN H. DAVIS** · 06-15-2015, 12:16 PM

Maybe:

Sub ks100x()
Dim x As Date
Dim y As Date
Dim rcell As Range
Dim ws As Worksheet
Dim colheader As Long, rowheader As Long
Set ws = ActiveSheet
y = Date
x = CDate("YOUR VARIABLE DATE")
With ws
For Each rcell In .Range("B2:ZZ500")
    If rcell.Value > y And rcell.Value < x Then
        colheader = rcell.column
        rowheader = rcell.row
    End If
    'Cells(rowheader, columnheader).Copy
    'YOUR OTHER CODE GOES HERE
Next rcell
End With
    
End Sub