Sort : how can I use a variable in a VB sort function?
 

If I create a variable and call it End_date how can I use it in the sort
function?
I want to replace the "02/26/06" value with this variable. I've tried just
replacing it with Cells.Find(What:=End_date,..... and it fails. I've also
tried placing it in brackets and that fails too.

Cells.Find(What:="02/26/06", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Sort : how can I use a variable in a VB sort function?
 

Hi El,
See last topic in Chip Pearson's
http://www.cpearson.com/excel/DateTimeVBA.htm

you have to effectively use a dateserial number the actual stored value of the date
as days past Dec 31, 1899 as recorded internally in Excel (actually it's the wrong value).

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"El Bee" wrote in message ...
If I create a variable and call it End_date how can I use it in the sort
function?
I want to replace the "02/26/06" value with this variable. I've tried just
replacing it with Cells.Find(What:=End_date,..... and it fails. I've also
tried placing it in brackets and that fails too.

Cells.Find(What:="02/26/06", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Sort : how can I use a variable in a VB sort function?
 

Forget the date value in the variable, let's say I want to find "110.05" one
time and the next time I want to find "Michael"; both values would be a
string. I want to use a variable name inside the find function. What is the
syntax for using this variable name instead of hard coded values as shown in
the example?

"David McRitchie" wrote:

Hi El,
See last topic in Chip Pearson's
http://www.cpearson.com/excel/DateTimeVBA.htm

you have to effectively use a dateserial number the actual stored value of the date
as days past Dec 31, 1899 as recorded internally in Excel (actually it's the wrong value).

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"El Bee" wrote in message ...
If I create a variable and call it End_date how can I use it in the sort
function?
I want to replace the "02/26/06" value with this variable. I've tried just
replacing it with Cells.Find(What:=End_date,..... and it fails. I've also
tried placing it in brackets and that fails too.

Cells.Find(What:="02/26/06", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Sort : how can I use a variable in a VB sort function?
 

I think you did fine with the expression.

But vba, dates, find don't always play nice.

dim End_Date as date
end_date = dateserial(2006,2,26)



Cells.Find(What:=end_date, ...
or
Cells.Find(What:=clng(end_date)

Sometimes, if you know the format that should match up...

Cells.Find(What:=format(end_date,"mm/dd/yy"), ...



El Bee wrote:

Forget the date value in the variable, let's say I want to find "110.05" one
time and the next time I want to find "Michael"; both values would be a
string. I want to use a variable name inside the find function. What is the
syntax for using this variable name instead of hard coded values as shown in
the example?

"David McRitchie" wrote:

Hi El,
See last topic in Chip Pearson's
http://www.cpearson.com/excel/DateTimeVBA.htm

you have to effectively use a dateserial number the actual stored value of the date
as days past Dec 31, 1899 as recorded internally in Excel (actually it's the wrong value).

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"El Bee" wrote in message ...
If I create a variable and call it End_date how can I use it in the sort
function?
I want to replace the "02/26/06" value with this variable. I've tried just
replacing it with Cells.Find(What:=End_date,..... and it fails. I've also
tried placing it in brackets and that fails too.

Cells.Find(What:="02/26/06", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate





--

Dave Peterson