Type mismatch error when referring to array element by location vba

Question

Since I'm new to VBA, I feel like I'm missing a fundamental concept. I'm attempting to access an element in an array at loc1 + 4th index. I continue to experience type mismatch errors. I would appreciate some assistance. I appreciate it.

Dim atype As Variant
Dim loc1 As Integer

atype = Worksheets("Inputs").Range("B21")

loc1 = InStr(atype, "Loan")
loanpct = atype(loc1 + 4)

Bond-61.87% Loan-38.13% is the value in my cell; I want to take the 38.13% portion. I can see from the comment below that it's not actually an array. Should I then try to turn the string into an array?

Answer 1

We utilise MID to parse the string. Unlike an array, which has things that may be accessed, a string does not have items.

Dim atype As String
Dim loc1 As Integer
Dim loanpct As Double

atype = Worksheets("Inputs").Range("B21")

loc1 = InStr(atype, "Loan")
loanpct = Application.Evaluate(Mid(atype, loc1 + 5))

Debug.Print loanpct

Loanpct is now a numeric value that can be utilized in future codes.

However, this is based on the idea that everything following a Loan- can be transformed into a number. It will fail if there is a non-numeric character after Loan.