I avoid using variants like the plague. They are handy when necessary but
trouble the rest of the time (not to mention very ineffient).
--
HTH...

Jim Thomlinson


"Andibevan" wrote:

Hi Jim,

Thanks for your comments, it provided me with enough info to get my solution
to work - I had incorrectly declared a variable- Here's my working code:-

Sub Search_RefNo()
Dim xAssNum_Val As String
Dim xAssName As String

Application.ScreenUpdating = False
Application.Run "Sort_RefNo"

xAssNum_Val = Range("Enter_RefNo")
xAssName = Format(xAssNum_Val, "0000")

Selection.AutoFilter Field:=1, Criteria1:=xAssName, Operator:=xlAnd
Application.Goto Reference:="Enter_RefNo"
Selection.ClearContents
Application.ScreenUpdating = True
End Sub


Thanks

Andi

"Jim Thomlinson" wrote in message
...
So there are no misunderstandings. The item in the spreadsheet is 0001 as
text. You want to pick up that value and use it in code as the criteria for
an autofilter, but somehow you are getting an implicit conversion of the
item
to an integer? Is your variable declared as a string? If not make sure to
change it. To convert an interger to a string in VBA there is a CStr()
function and a format(number, format) function. Without seeing your code it
is a little difficult to comment on the best solution, but that should be a
start.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Tom has given you the VBA method. Here is how to do it without VBA. Select
the cells in the range. Right Click. Format Cells. Custom - 0000
--
HTH...

Jim Thomlinson


"Andibevan" wrote:

Hi All,

I have a cell with a named range Enter_RefNo

I want to format this variable in the format as follow

1 -- 0001
10 -- 0010
11 -- 0011

And so on

Any ideas?

Regards

Andi