Compile Error Array Expected - Ubound - VBA

Question

I am encountering a error Compile Error Array Expected. Am I using Ubound incorrectly? Should I declare myarray("Measurement", "", "Nominal", "Low", "High", "", "") With a Dim statement at the opening? If so then how to do I hande the rest of the statement?

   Private Sub Format_Parse_Replace()
    Dim i As Double
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    Dim NomValue As String
    Dim myarray As String


    Dim rLastCell As Range
                Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
                xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    'MsgBox ("The last used column is: " & rLastCell.Column)
        Rows("4:6").EntireRow.Insert

        myarray = Array("Measurement", "", "Nominal", "Low", "High", "", "")
            Range("A2:A" & UBound(myarray) + 1) = _
            WorksheetFunction.Transpose(myarray)

    For i = rLastCell.Column To 2 Step -1
            'If Cells(1, i) = "Logbook" Then
                'Columns(i).Delete
            'End If
            'Set Col = ColLett(rLastCell.Column)
           NomValue = ws.Cells(3, i).Value
           Columns(i).Cells(4).NumberFormat = "@"
           Columns(i).Cells(5).NumberFormat = "@"
           Columns(i).Cells(6).NumberFormat = "@"
           Columns(i).Cells(4) = SplitString(NomValue, ",", 4)
           Columns(i).Cells(5) = SplitString(NomValue, ",", 5)
           Columns(i).Cells(6) = SplitString(NomValue, ",", 6)

    Next i
    Rows(3).Delete

    End Sub

Thank you

Ron Rosenfeld · Accepted Answer · 2015-05-07 15:39:04Z

5

Excel Help: Array Function: Returns a Variant containing an array.

Dim myarray As String

Change to

Dim myarray As Variant

answered May 7, 2015 at 15:39

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compile Error Array Expected - Ubound - VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related