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

Range("Enter_RefNo").NumberFormat = "0000"

--
Regards,
Tom Ogilvy

"Andibevan" wrote in message
...
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

Sorry Tom, I wasn't clear enough - I am using the value Enter_RefNo in a
piece of VBA code and need the number format to remain as 0000 in the code.

I.e. if I put 0001 into Enter_RefNo, currently that gets picked up as "1"
whereas I need it to be formatted as "0001" in order to get a match later in
the code.

Thanks

Andi

"Tom Ogilvy" wrote in message
...
Range("Enter_RefNo").NumberFormat = "0000"

--
Regards,
Tom Ogilvy

"Andibevan" wrote in message
...
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

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

Jim, sorry I wasn't clear enough.

I want to use the number from Enter_RefNo in an autofilter. The problem is
that the worksheet cell Enter_RefNo is formatted as 0000, but when it is
inserted using my code just the number 1 is inserted, not 0001.

As a result no results are returned.

"Jim Thomlinson" wrote in message
...
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

Jim, sorry I wasn't clear enough.

I want to use the number from Enter_RefNo in an autofilter. The problem is
that the worksheet cell Enter_RefNo is formatted as 0000, but when it is
inserted using my code just the number 1 is inserted, not 0001.

As a result no results are returned.

"Jim Thomlinson" wrote in message
...
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

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

Dim v as String
' if the cell Enter_RefNo is displaying 0001 then

v= Range("Enter_RefNo").Text

or
' if it displays 1
v = format(Range("Enter_RefNo").Value,"0000")

or with v containing "1" or 1

v = format(clng(v),"0000")

or

v = Right(v & "0000",4)


--
Regards,
Tom Ogilvy


"Andibevan" wrote in message
...
Sorry Tom, I wasn't clear enough - I am using the value Enter_RefNo in a
piece of VBA code and need the number format to remain as 0000 in the
code.

I.e. if I put 0001 into Enter_RefNo, currently that gets picked up as "1"
whereas I need it to be formatted as "0001" in order to get a match later
in
the code.

Thanks

Andi

"Tom Ogilvy" wrote in message
...
Range("Enter_RefNo").NumberFormat = "0000"

--
Regards,
Tom Ogilvy

"Andibevan" wrote in
message
...
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

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

Thanks Tom - that gives me a good idea of a number of other custom number
formats as well as my solution.


"Tom Ogilvy" wrote in message
...
Dim v as String
' if the cell Enter_RefNo is displaying 0001 then

v= Range("Enter_RefNo").Text

or
' if it displays 1
v = format(Range("Enter_RefNo").Value,"0000")

or with v containing "1" or 1

v = format(clng(v),"0000")

or

v = Right(v & "0000",4)


--
Regards,
Tom Ogilvy


"Andibevan" wrote in message
...
Sorry Tom, I wasn't clear enough - I am using the value Enter_RefNo in a
piece of VBA code and need the number format to remain as 0000 in the
code.

I.e. if I put 0001 into Enter_RefNo, currently that gets picked up as "1"
whereas I need it to be formatted as "0001" in order to get a match later
in
the code.

Thanks

Andi

"Tom Ogilvy" wrote in message
...
Range("Enter_RefNo").NumberFormat = "0000"

--
Regards,
Tom Ogilvy

"Andibevan" wrote in
message
...
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

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