CODEWITHSUNDEEP
excelvba
Tom Ruh
Tom Ruh

Reputation: 336

Compile Error Array Expected - Ubound - VBA

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

Upvotes: 2

Views: 2938

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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

Dim myarray As String

Change to

Dim myarray As Variant

Upvotes: 5

Related Questions