Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a range variable inside a excel function | Excel Discussion (Misc queries) | |||
How to sort number with text in large function? | Excel Worksheet Functions | |||
A function to get a variable row reference for range in XNPV funct | Excel Worksheet Functions | |||
Possible Variable in Rate Function | Excel Worksheet Functions | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |