CODEWITHSUNDEEP
excelvba
HeatherD
HeatherD

Reputation: 67

Concatenating Using Case Statement with Multiple Conditions

I'm trying to concatenate Tier Code and Cap Code where Cap Level = 2. I was able to successfully do the concatenation earlier using a case statement, without the condition that Cap Level = 2. However, now that I'm trying to add that condition, I keep getting the error "Type Mistmatch". I've tried setting Range("E3:E24").Value = 2 and = "2". Both ways, I still get the error.

My data currently looks like this:

enter image description here

I've been playing around with this for at least an hour now and reading other questions about case statements with multiple conditions/criteria, but I haven't been able to get my code to work properly.

Sub Concat_ParentCode_Cap1_001()

With Worksheets("PD Code Structure")

Dim ParentCode As Range
Dim TierCode As String
Dim CapCode As String

CapCode = "FS_CAP_1_001"
TierCode = "FS_Tier_1"
Set ParentCode = Range("F3:F24")

        Select Case True
            Case CapCode = "FS_CAP_1_001" And Range("E3:E24").Value = "2"
                ParentCode = TierCode & "." & CapCode
        End Select

End With
End Sub

I would like my data to look like this:

enter image description here

Upvotes: 0

Views: 462

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27239

There are a few things in the code provided that will cause it not too work, but the main thing is a loop to check each cell.

Give this a shot:

Option Explicit

Sub Concat_ParentCode_Cap1_001()

    Dim tierCode As String
    tierCode = "FS_TIER_1."

    With Worksheets("PD Code Structure")

        Dim capCode As Range

        For Each capCode In .Range("F2:F24")

            If Len(capCode.Offset(, -1)) Then

                Dim capParent As String
                capParent = capCode.Value

            End If

            If capCode.Offset(, -2).Value = 2 Then
                capCode.Offset(, -1).Value = tierCode & capParent
            End If

        Next

    End With

End Sub

Upvotes: 1

Olabode
Olabode

Reputation: 25

Do you really need to use VBA for this ? This looks like something you can do with just an if statement on excel. =if(B2=2,CONCAT(A1,".",D1),""). This is of course assuming the columns are listed A-D above.

Upvotes: 0

Related Questions