Hi Sudhendra,

Yes:

If InStr(1, ZoneNo, "us", vbTextCompare) Then _
sREPORT_CODE = "999" Else sREPORT_CODE = _
Format(ZoneNo, "000")

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Sudhendra wrote:
Hi

Can this code be written in one line!!!! thanks in advance

If (InStr(1, ZoneNo, "us", vbTextCompare) 0) Then
sREPORT_CODE = "999"
ElseIf ZoneNo = 1 Then
sREPORT_CODE = Format
(ZoneNo, "000")
ElseIf ZoneNo = 2 Then
sREPORT_CODE = Format
(ZoneNo, "000")
ElseIf ZoneNo = 3 Then
sREPORT_CODE = Format
(ZoneNo, "000")
ElseIf ZoneNo = 4 Then
sREPORT_CODE = Format
(ZoneNo, "000")
ElseIf ZoneNo = 5 Then
sREPORT_CODE = Format
(ZoneNo, "000")
End If