CODEWITHSUNDEEP
excelvba
Baber62
Baber62

Reputation: 53

Concatenate with select case

In my spreadsheet I have time give in the following formats: 1415 or 415 or 45 or 5. I need to convert this to 14:15 or 04:15, 00:45, or 00:05.

Here's the coding I have tried but it gives me the error message:

Compile error: Sub or function not defined

Dim LENGTH As Integer
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Offset(1, 0).Range("A1").Select
a = ActiveCell.Value
LENGTH = Len(a)
    Select Case LENGTH
        Case Is = 4
            B = Value(Concatenate(Left(a, 1), ":", Right(a, 2)))
        Case Is = 3
            B = Value(Concatenate("0", Left(a, 1), ":", Right(a, 2)))
        Case Is = 2
            B = Value(Concatenate("00", ":", Right(a, 2)))
        Case Is = 1
            B = value(Concatenate("00:0", ":", Right(a, 1)))
    End Select
ActiveCell.Value = B
Selection.NumberFormat = "hh:mm"
Loop

Upvotes: 1

Views: 279

Answers (1)

Ulli Schmid
Ulli Schmid

Reputation: 1167

You are trying to use Excel Formulas like VBA functions.
You may do that sometimes, but only using WorksheetFunction.

Example:

Sum(1, 2, 3)

won't work.

WorksheetFunction.Sum(1, 2, 3)

will work.

Important: Most, but not all WorksheetFunctions are accessible in VBA. As a general rule those functions that have an equivalent VBA solution are not accessible through WorksheetFunction.

Concatenate, for instance, is not. You can, however, concatenate strings with &:

"Apple " & "Pie" -> "Apple Pie"

Upvotes: 2

Related Questions