'Find' with block variable not set :/

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hiya,

I'm trying to find cells in a range that have a date >= today.... this wont work:

Code:
Set Cell = .Find(What:=Cell.Value >= Date, After:=.Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)

Can it be adapted at all?

Thanks!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Short answer - no.:)

You can't use the result of a comparison to look for a date.

A comparison wille result in either boolean true/false

What are you actually trying to achieve?
 
Upvote 0

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hi Norie

What i am trying to do is search a range (rng) for project dates which correspond with today or in the future, and then bring in matching project names from the 1st column. This is what i have:

Code:
Private Sub CommandButton2_Click()
    Dim Sh As Worksheet
    Dim rng As Range
    Dim R As Integer, LR As Long
    Dim Cell As Range
    Dim FirstAddr As String
    Set Sh = Worksheets("Project")
    LR = Sh.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Sh.Range("A2:CJ" & LR)
    R = 0
    Range("B14:B41").Value = ""
    With rng
        Set Cell = .Find(What:=Cell.Value >= Date, After:=.Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
        If Not Cell Is Nothing Then
            FirstAddr = Cell.Address
            Do
                With Range("B14")
                    .Offset(0, R).Value = Cell.EntireRow.Cells(1, 1).Value
                    R = R + 1
                End With
            Set Cell = .FindNext(Cell)
            Loop While Not Cell Is Nothing And Cell.Address <> FirstAddr
        End If
    End With
End Sub

Basically, this is a huge sheet of projects, and a load of columns with different dates in the cells.
 
Upvote 0

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
This works well, but obviously this would only give me todays matches based on the bln 1/0:

Code:
Dim Sh As Worksheet
    Dim rng As Range
    Dim R As Integer, LR As Long
    Dim Cell As Range
    Dim FirstAddr As String
    Set Sh = Worksheets("Project")
    LR = Sh.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Sh.Range("A2:CJ" & LR)
    R = 0
    Range("B14:B41").Value = ""
    With rng
        Set Cell = .Find(What:=Date, After:=.Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
        If Not Cell Is Nothing Then
            FirstAddr = Cell.Address
            Do
                With Range("B14")
                    .Offset(R, 0).Value = Cell.EntireRow.Cells(1, 1).Value
                    R = R + 1
                End With
            Set Cell = .FindNext(Cell)
            Loop While Not Cell Is Nothing And Cell.Address <> FirstAddr
        End If
    End With
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Have you considered not using Find?

Exactly what is hard to say without some data and further explanation, perhaps some sort of filter.

Using Find with dates in VBA can be quite problematic, if not nigh on impossible.:)
 
Upvote 0

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Problem is my dates aren't in a nice neat column... they are essentially scattered within an array along the lines of D2:BZ100!

I'm open to suggestions :>
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Right I'll go get my crystal ball.:)

Please post at least a snippet of data.
 
Upvote 0

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
If the dates are formatted in a consistent fashion, consider using the Application object's FindFormat property.

Problem is my dates aren't in a nice neat column... they are essentially scattered within an array along the lines of D2:BZ100!

I'm open to suggestions :>
 
Upvote 0

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Ok, solved it with a loop, although it's not exactly efficient :s

Code:
    Dim Sh As Worksheet
    Dim rng As Range, vardate1 As Date
    Dim R As Integer, LR As Long
    Dim Cell As Range
    Dim FirstAddr As String
    Set Sh = Worksheets("Project")
    LR = Sh.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Sh.Range("A2:CJ" & LR)
    R = 0
    Range("B14:B41").Value = ""
    vardate1 = Date
    Do While vardate1 <= (Date + 90)
    With rng
        Set Cell = .Find(What:=vardate1, After:=.Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
        If Not Cell Is Nothing Then
            FirstAddr = Cell.Address
            Do
                With Range("B14")
                    .Offset(R, 0).Value = Cell.EntireColumn.Cells(1, 1).Value
                    .Offset(R, 1).Value = Cell.EntireRow.Cells(1, 3).Value
                    .Offset(R, 2).Value = vardate1
                    R = R + 1
                End With
            Set Cell = .FindNext(Cell)
            Loop While Not Cell Is Nothing And Cell.Address <> FirstAddr
        End If
    End With
    vardate1 = vardate1 + 1
    Loop

Now, the code loops through each day for the next 90 days...
 
Upvote 0

Forum statistics

Threads
1,191,025
Messages
5,984,201
Members
439,877
Latest member
kellylet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top