What is the proper dot.syntax to insert a select case within a With /End With Statement
 

For Instance,

Dim LRow As Long
Dim CostCode As String
Dim Vendor As String
With Sheets("Alpha")
LRow = Cells(Row,Count,1).End(xlUp).Row
Set CostCode = .Range("A2:A" & LRow).Value
Set Vendor = .Range("B2:B" & LRow).Value
Select Case CostCode
Case "<063" 'If CostCode is < "063" Numbers as Text
Do this
Case "304"
Do that
Case "386"
Select Case Vendor
Case "Martin"
Do This
Case "Charles"
Do That
End Select
End Select
End With
My syntax is approximative
Help appreciated,

J.P.

What is the proper dot.syntax to insert a select case within a With / End With Statement
 

Try...

Dim LRow As Long
With Sheets("Alpha")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Select Case CLng(.Range("A2:A" & LRow).Value)
Case Is < 63 'If CostCode is < "063" Numbers as Text
'Do this
Case 304
'Do that
Case 386
Select Case .Range("B2:B" & LRow).Value
Case "Martin"
'Do This
Case "Charles"
'Do That
End Select
End Select
End With

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

What is the proper dot.syntax to insert a select case within aWith / End With Statement
 

Thank you Garry, but my cases are Alphas, not Numerics. "<063", "304", "386" etc...
..
Will the following statement change to handle Alphas ?
Select Case CLng(.Range("A2:A" & LRow).Value)
..
Thank you for your help.

J.P.

What is the proper dot.syntax to insert a select case within a With / End With Statement
 

Thank you Garry, but my cases are Alphas, not Numerics. "<063",
"304", "386" etc... .

Not correct!
Your cases are 'numeric text', not alphas!

Will the following statement change to handle Alphas ?
Select Case CLng(.Range("A2:A" & LRow).Value)
.
Thank you for your help.

J.P.

Yes, it adapts your 'numeric text' so the Select Case works properly.
Note that this will only work if your value are 'numeric text', meaning
no alpha characters allowed.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

đọc truyện tranh rockin' heaven Thầy giáo : Em hãy cho biết Mặt Trăng xa hơn hay Mặt Trời xa hơn?
Trò : Mặt trời xa hơn ạ .
Th̀y : Vì sao ?
Trò : Vì sao của Khởi My ạ
Th̀y : ko, tại sao ?
Trò : Tại sao của Ưng Hoàng Phúc ạ !
Th̀y : Ko , ý th̀y là Why đó .!
Trò : Why ? À ! Why của DBSK .
Th̀y : Trời ơi ,tôi phải làm thế nào ? Xem thêm xem truyện tranh gay 18

What is the proper dot.syntax to insert a select case within aWith / End With Statement
 

How do I incorporate your statement
Select Case CLng(.Range("A2:A" & LRow).Value)

into my actual Select Case macro
Sub Test()
Dim LRow As Long
Dim rngC As Range
Dim myStr As String
With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("M3:M" & LRow)
Select Case rngC
Case Is < 63 '
myStr = Format(rngC, "000")
Format Error here when actual data can be 002, 063 or 386.
My Data type is 1
Thank you for your help.
J.P.

What is the proper dot.syntax to insert a select case within a With / End With Statement
 

How do I incorporate your statement
Select Case CLng(.Range("A2:A" & LRow).Value)

into my actual Select Case macro
Sub Test()
Dim LRow As Long
Dim rngC As Range
Dim myStr As String
With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("M3:M" & LRow)

Select Case CLng(rngC.Value)

Case Is < 63 '
myStr = Format(rngC, "000")
Format Error here when actual data can be 002, 063 or 386.
My Data type is 1
Thank you for your help.
J.P.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion