I’m a VBA Noob, and I feel like I’m missing something basic here. I have an array, and I’m trying to access an element at the loc1 + 4th index. I keep getting the type mismatch error. Could someone please help me. Thanks in advance!
Dim atype As Variant
Dim loc1 As Integer
atype = Worksheets("Inputs").Range("B21")
loc1 = InStr(atype, "Loan")
loanpct = atype(loc1 + 4)
My cell value is Bond-61.87% Loan-38.13%, I want to extract the 38.13% part. With the comment below, I understand that its actually not an array. So should I try to convert the string to an array then?
>Solution :
To parse the string we use MID. The string is not an array with items that can be referenced like an array.
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
Now loanpct is a number and can be used in later code.
But this assumes that everything after Loan- can be converted to a number. If there is any non numeric character after Loan- then it will fail.