Finding a value "less than" a variable.

[exRP12Nuke]

Good afternoon,

I am trying to find and select rows that are less than a particular date. Here is my code so far T1 is the date that I am trying to find.

Sub MoveData()

    x = Range("T1")
    Dim LRS As Long
    Dim Dx As String
    Range("H:H").Select
        If WorksheetFunction.CountA(Cells) > 0 Then
            LRS = Selection.Find(What:="<x", After:=[H1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        End If
End Sub

What do I need to have inplace of "<x" to find values that are less than x?

Thanks!

 

[SkipVought]

Hi,

How about using the COUNTIF() spreadsheet funtion?

Do you REALLY need VBA to do this?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[exRP12Nuke]

No, I do not really need VBA to accomplish this, but it is a small part of a larger formatting macro that I have been working on. I want to be able to find the rows of data that occur prior to a set date and copy them over to a different sheet within the workbook.

Many people will be using this program, and I am just trying to make it as user friendly as possible with as few steps as possible.

Thanks!

 

[SkipVought]

find the rows of data that occur prior to a set date and copy them over to a different sheet within the workbook.

Your could find the rows that qualify, using the AutoFilter on the date column, or you could use MS Query to return the data you want to the other sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[exRP12Nuke]

The dates are always changing, so using the autofilter code for VBA would not work (unless there is a way to use variables for that). I will look into MS Query when I get back to work tomorrow.

Thanks!

 

[SkipVought]

Turn on your macro recorder and record setting the date filter as required for a specific set of dates.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I would follow Skip's advice, but for future reference I would try:

LRS = Selection.Find(What:=[blue]"<" & x[/blue], After:=[H1],

Have fun.

---- Andy

 

[exRP12Nuke]

Thanks guys! Here is the starting point that I ended up with:

x = Range("T1")
    Range("H:H").Select
        If WorksheetFunction.CountA(Cells) > 0 Then
            LRS = Selection.Find(What:="*", After:=[H1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        End If
    Sx = "S" & LRS
    If Range("H3") > x Then GoTo ErrHandler2
    
    ActiveSheet.Range("$A$2", Sx).AutoFilter Field:=8, Criteria1:="<" & x

A lot of extra work on my end to make it simpler for others.

Thanks!