I'm relatively new to VBA, and I'm trying to automate some macros for a datasheet. I need it to look at the value known as rackRack.Text (I know, not the best var name) and split it. The original value would look like: "B-04". I want it to split and select a case based on the first letter of the cell. I keep getting a type mismatch, assuming this is because "avarSplit" is a variant. but I can't get it to compare anything. Any advice?

Private Sub addRack_Click() 'Add rack mount server

Sheets("Data Center Equipment").Select

Dim devType, name, desc, rack, ddd, asset, pduA, pduB, pduC As String
Dim uLoc As Long
Dim FoundRange As Range
Dim avarSplit As Variant
devType = typeRack.Text
name = nameRack.Text
desc = descRack.Text
rack = rackRack.Text
uLoc = uRack.Text
ddd = dddRack.Text
asset = assetRack.Text
pduA = pduABox.Text
pduB = pduBBox.Text
pduC = pduCBox.Text


Set FoundRange = Sheets("Data Center Equipment").Columns("E:E").Find(what:=rackRack.Text, LookIn:=xlFormulas, lookat:=xlWhole)
    
    If FoundRange Is Nothing Then
        UserForm1.Hide
        UserForm2.Show
    Else
        FoundRange.Select
        ActiveCell.Offset(0, 1).Select
        Do
            ActiveCell.Offset(1, 0).Select
        Loop Until ActiveCell.Value > uRack.Text
        ActiveCell.EntireRow.Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        With ActiveCell
            Cells(.Row, "B").Select
            ActiveCell.Value = devType
            Cells(.Row, "C").Select
            ActiveCell.Value = name
            Cells(.Row, "D").Select
            ActiveCell.Value = desc
            Cells(.Row, "E").Select
            ActiveCell.Value = rack
            Cells(.Row, "F").Select
            ActiveCell.Value = uRack.Text
            
            avarSplit = Split(rackRack.Text, "-")
            ActiveCell.Value = avarSplit
                Select Case avarSplit
                    Case "B*", "D*", "F*", "G*"
                        With ActiveCell
                            Cells(.Row, "M").Select
                            ActiveCell.Value = pduA
                            Cells(.Row, "Q").Select
                            ActiveCell.Value = pduB
                            Cells(.Row, "U").Select
                            ActiveCell.Value = pduC
                        End With
                    Case Else
                        With ActiveCell
                            Cells(.Row, "L").Select
                            ActiveCell.Value = pduA
                            Cells(.Row, "P").Select
                            ActiveCell.Value = pduB
                            Cells(.Row, "T").Select
                            ActiveCell.Value = pduC
                        End With
                End Select
    
            Cells(.Row, "AB").Select
            ActiveCell.Value = ddd
            Cells(.Row, "AF").Select
            ActiveCell.Value = asset
            ActiveCell.EntireRow.Select
        End With

    End If

End Sub

Split returns an array, so you have to reference your variable as an array.

So, for example, if you reference avarSplit(0) you will get the first element in the array, avarSplit(1) will get you the second, etc.

This article has been dead for over six months. Start a new discussion instead.