PDA

View Full Version : Need help for "Format" function



avadhutd2
08-27-2009, 01:52 AM
Hi,

I have a question regarding the format function.

My requirement to verify an ID and fetch data from another sheet corresponding to that ID.

Hence here I need to format a number which is seen as 1-23456 or (1)23456 to 123456 as that will help me for comparison.
I tried the code below, however that is not giving me the results anticipated.

If (IsNumeric(ActiveSheet.Range("H" & lngCtr))) Then
ActiveSheet.Range("H" & lngCtr) = _
Format(ActiveSheet.Range("H" & lngCtr), "000000")
End If

Can anyone give me further inputs?

Thanks in advance!

Bob Phillips
08-27-2009, 02:30 AM
If (IsNumeric(ActiveSheet.Range("H" & lngCtr))) Then

With ActiveSheet.Range("H" & lngCtr)

.Value = Replace(Replace(Replace(.Value, _
"-", ""), _
"(", ""), _
")", "")
End With
End If

avadhutd2
08-27-2009, 03:11 AM
Thanks for the reply...When I tried ID as 1-23456 or (1)23456, I am still getting same result.

Bob Phillips
08-27-2009, 03:32 AM
I missed the IF bit, which stops it working because the value is not numeric



With ActiveSheet.Range("H" & lngCtr)

.Value = Replace(Replace(Replace(.Value, _
"-", ""), _
"(", ""), _
")", "")
End With