VBA Type mismatch

[Nervatos]

Hello there

I have tried to debug this code, but get this error type mismatch, I can't see where I falied.

Sub testt()
    Dim i As Long
    Dim myArray, asArray, X As String
    X = "12k"

    myArray = Array("Y", "Z", "E", "P", "T", "G", "k", "h", "da", "d", "c", "m", "µ", "n", "p", "f", "a", "z", "y")
    asArray = Array(X * WorksheetFunction.Power(10, 24), _
                    X * WorksheetFunction.Power(10, 21), _
                    X * WorksheetFunction.Power(10, 18), _
                    X * WorksheetFunction.Power(10, 15), _
                    X * WorksheetFunction.Power(10, 12), _
                    X * WorksheetFunction.Power(10, 9), _
                    X * WorksheetFunction.Power(10, 6), _
                    X * WorksheetFunction.Power(10, 3), _
                    X * WorksheetFunction.Power(10, 2), _
                    X * WorksheetFunction.Power(10, 1), _
                    X * WorksheetFunction.Power(10, -1), _
                    X * WorksheetFunction.Power(10, -2), _
                    X * WorksheetFunction.Power(10, -3), _
                    X * WorksheetFunction.Power(10, -6), _
                    X * WorksheetFunction.Power(10, -9), _
                    X * WorksheetFunction.Power(10, -12), _
                    X * WorksheetFunction.Power(10, -15), _
                    X * WorksheetFunction.Power(10, -18), _
                    X * WorksheetFunction.Power(10, -21), _
                    X * WorksheetFunction.Power(10, -24))
   
    For i = LBound(myArray) To UBound(myArray)
        If Right(X, 1) Like myArray(i) Then
            Debug.Print asArray(i)
        End If
    Next i
End Sub

My error comes in

asArray = Array(X * WorksheetFunction.Power(10, 24), _

                    X * WorksheetFunction.Power(10, 21), _

                    X * WorksheetFunction.Power(10, 18), _

                    X * WorksheetFunction.Power(10, 15), _

                    X * WorksheetFunction.Power(10, 12), _

                    X * WorksheetFunction.Power(10, 9), _

                    X * WorksheetFunction.Power(10, 6), _

                    X * WorksheetFunction.Power(10, 3), _

                    X * WorksheetFunction.Power(10, 2), _

                    X * WorksheetFunction.Power(10, 1), _

                    X * WorksheetFunction.Power(10, -1), _

                    X * WorksheetFunction.Power(10, -2), _

                    X * WorksheetFunction.Power(10, -3), _

                    X * WorksheetFunction.Power(10, -6), _

                    X * WorksheetFunction.Power(10, -9), _

                    X * WorksheetFunction.Power(10, -12), _

                    X * WorksheetFunction.Power(10, -15), _

                    X * WorksheetFunction.Power(10, -18), _

                    X * WorksheetFunction.Power(10, -21), _

                    X * WorksheetFunction.Power(10, -24))

I have tried to make this: SI Prefixes Unit Conversion Table - Tutorial, Definition, Example

Hope some can resolve it for me.
Thanks.

 

[Micron]

My error comes in

Which line? My guess is that it is because you declared X as a string and are then trying to use math functions on it. Try declaring x as Long.

 

[Rick Rothstein]

You assigned "12k" to the X variable which is not a number and then attempted to multiply by it in the next statements. Maybe assign 12000 to the variable instead? Also, you should then declare X as a Long instead of a String.

 

[Nervatos]

Hello guys

My error comes in the whole line with asArray.
I have tried to make X as A Long instead of a String. Now I got the same error in this X = "12k".
Thanks for your replies.

 

[Zyndstoff]

"12k" is not a numerical, it is a string.
Change to X = 12000

 

[Rick Rothstein]

As I said in my previous post, 12K is not a number so you cannot multiply by it. I think you want it to be 12000.

 

[Nervatos]

But I need it to paste a letter after the number, so it calculate after what letter it is.

 

[JEC]

This is the only thing you need

Sub jec()
 Dim ar1, ar2, sq As Variant
 Dim X As String
 
 X = "12k"
 ar1 = Array("Y", "Z", "E", "P", "T", "G", "k", "h", "da", "d", "c", "m", "µ", "n", "p", "f", "a", "z", "y")
 ar2 = Array(24, 21, 18, 15, 12, 9, 6, 3, 2, 1, -1, -2, -3, -6, -9, -12, -15, -18, -21, -24)
       
 sq = Application.Match(Right(X, 1), ar1, 0)
 
 If IsNumeric(sq) Then
     Debug.Print Val(X) * (10 ^ ar2(sq - 1))
 Else
    MsgBox "no match"
 End If
End Sub

 

[Nervatos]

Thank you JEC! That was perfect.

 

[JEC]

You're welcome!