How do I identify different strings in a Range with a Case?

AFGPHXExcel
AFGPHXExcel used Ask the Experts™
on
I have a range of 4 cells. Each cells can contain a particular phrase. I would like to use a case to identify if there is a string in any of the cells in the Range. Current the code can only look at one of the cells at a time. The code:

    Select Case Range("Rd_Reviewer1ReqBy_Attachment1").Value
   
    Case "LOJ"
    v_WksNames = Array("RequirementSummary", "Reviewer1", "LOJ")
    Call M_CreateMultiWksFile(v_WksNames)
   
    Case "Review File - ACO"
     v_WksNames = Array("Reviewer2")
    Call M_CreateMultiWksFile(v_WksNames)
   
    Case "Review File - Camp Mayor"
     v_WksNames = Array("Reviewer3")
    Call M_CreateMultiWksFile(v_WksNames)
   
    Case "Review File - Chief of Staff Approving O6"
     v_WksNames = Array("Reviewer8")
    Call M_CreateMultiWksFile(v_WksNames)
   
    End Select

Any help would be much appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
You need a loop:

    Dim RangeName As Variant

    For Each RangeName In Array("Rd_Reviewer1ReqBy_Attachment1", "Rd_Reviewer1ReqBy_Attachment2", "Rd_Reviewer1ReqBy_Attachment3", "Rd_Reviewer1ReqBy_Attachment4")

    Select Case Range(RangeName).Value
   
    Case "LOJ"
    v_WksNames = Array("RequirementSummary", "Reviewer1", "LOJ")
    Call M_CreateMultiWksFile(v_WksNames)
   
    Case "Review File - ACO"
     v_WksNames = Array("Reviewer2")
    Call M_CreateMultiWksFile(v_WksNames)
   
    Case "Review File - Camp Mayor"
     v_WksNames = Array("Reviewer3")
    Call M_CreateMultiWksFile(v_WksNames)
   
    Case "Review File - Chief of Staff Approving O6"
     v_WksNames = Array("Reviewer8")
    Call M_CreateMultiWksFile(v_WksNames)
   
    End Select

    Next RangeName

Kevin
Maybe you can use

if cell1="..." then
    .....
elseif cell2="..." then
    .....
elseif cell3="..." then
    .....
elseif cell4="..." then
    .....
endif

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial