Sorting with a variable

[hyoung3]

I am having difficulty figuring out how to sort with a variable. I have a macro that used a given cell value as a variable. I have named this variable "a". I have checked to make sure the variable is set to the value I want from the cell selected. I want to now use this variable as a sorting criteria. Here is the code I used to try and do this.

ActiveSheet.Range("$A$1:$T$" & r).AutoFilter Field:=5, Criteria1:= _ "<" & a, Operator:=xlAnd

 

[Jonmo1]

Can you show the code that worked?

Then also the code you're trying to use, including the line that assigns a value to the variable a

And what type of data is your variable? Is it a date?

 

[hyoung3]

A is a time variable and the value for this specific operation is 113100. The sorting work if "Criteria1:="<113300", but I want to substitute the 113300 with a because the time is constantly changing.

Dim a As Single
a = Cells(r - 1, 5).Value
ActiveSheet.Range("$A$1:$T$" & r).AutoFilter Field:=5, Criteria1:= _
"<" & a, Operator:=xlAnd

 

[cgcamal]

Hi hyoung3,

It should work like this (without _ after :=):

ActiveSheet.Range("$A$1:$T$" & r).AutoFilter Field:=5, Criteria1:= [COLOR=Red]"<" & a[/COLOR], Operator:=xlAnd

Or you can define a string variable as follow:

Var="<3"
or 
Var = "<" & a 
ActiveSheet.Range("$A$1:$T$" & r).AutoFilter Field:=5, Criteria1:=[COLOR=Red]Var[/COLOR], Operator:=xlAnd

Hope this helps,

Regards

 

[Jonmo1]

What exactly is in Cells(r - 1, 5) ?

is it a real time value like 11:33:00 ?
or just the number 113300 ?

Format that cell as a NUMBER with 6 decimals.
What does it show?

 

[hyoung3]

It shows 113100. I got it to work. I really apologize for the confusion. I couldn't post the whole code because it is long. What happened was the code was working correctly, but I didn't know it because I was trying to filter and delete everything less than 113100. That was actually everything because there was not a time greater than that.

Thank you both for your help. I really appreciate it!!!