Type mismatch error when referring to array element by location vba

0 votes

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?

Feb 11, 2023 in Others by narikkadan
• 63,720 points
714 views

1 answer to this question.

0 votes

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.

answered Feb 11, 2023 by Kithuzzz
• 38,020 points

Related Questions In Others

0 votes
1 answer

Excel VBA to change background image of shape by clicking on shape

You need to keep track of what ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
2,751 views
0 votes
1 answer

How to modify Powerpoint Chart ChartData by Excel VBA

Example: Code: Set pptApp = GetObject(, "PowerPoint.Application") Set pptPres = ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,720 points
2,937 views
0 votes
1 answer

VBA to unhide a column when opening a workbook

Use the workbook_open even within ThisWorkbook. READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,720 points
465 views
0 votes
1 answer

Excel VBA Type Mismatch calling Private Sub from ListBox_Click

Confusion: You must declare the parameter as ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,720 points
653 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,112 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,544 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
829 views
0 votes
1 answer

How to make an error flagging array in VBA and translate all array elements as a string message?

In my opinion, using an array in ...READ MORE

answered Mar 17, 2023 in Others by Kithuzzz
• 38,020 points
490 views
0 votes
1 answer

VBA runtime error 1004 when copy and pasting

Try this: Sub CopyWorksheet() Dim ...READ MORE

answered Feb 23, 2023 in Others by Kithuzzz
• 38,020 points
985 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP