Split a string

Question

Split a string

Asked 9 years, 9 months ago

Viewed 1,041 times

3

I’m trying to make a simple code using the split, but some mistakes are occurring that I’m not able to solve.. no if with asterisk VBA informs me that the subscript this out of range..

Sub Getinstrument()
Dim instrument As String
Dim splitinstrument() As String
Dim i As Integer
Dim removespax As Integer
Dim tam As Integer
removespax = -1
'Set instrument = Range(ActiveCell.Row, B)

instrument = Range("E3")
splitinstrument() = Split(instrument)
tam = Len(instrument) - 1
  For i = 0 To tam
**If splitinstrument(i) <> "" And "x" Then
    removespax = removespax + 1
    splitinstrument(removespax) = splitinstrument(i)
End If
Next
ReDim Preserve splitinstrument(removespax)
MsgBox splitinstrument()
End Sub

Really weird code... You should look at the size of the array itself to go through it; instead, you are orienting yourself by the size of the range instrument. You need to debug and identify the content of splitinstrument. This array may be empty.

– Caffé

2014/09/10 at 16:23
i made the following modifications to the code

– MOISES

2014/09/10 at 19:26
eu fiz as seguintes modificacoes no codigo instrument = Range("E3")
splitinstrument() = Split(instrument)
 
For i = LBound(splitinstrument) To UBound(splitinstrument)
 If splitinstrument(i) <> "" Then
 removespax = removespax + 1
 splitinstrument(removespax) = splitinstrument(i) End If Next I will debug the code and VBA keeps giving error in splitinstrument type 'type Mismatch'. I tried to use the mid function to separate my string but continued with the same problem.

– MOISES

2014/09/10 at 19:41
Please update your question with the new code and inform the line that the error occurs.

– Caffé

2014/09/10 at 19:47

1 answer

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.

by Ulisses • **363** points · Answer 1 · 2014-09-29T11:31:09+00:00

In Excel, the arrays start at position 1. In the code snippet below, variable i starts with 0, and when evaluating splitinstrument(0) error occurs.

tam = Len(instrument) - 1
For i = 0 To tam
If splitinstrument(i) <> "" And "x" Then

I am not on a computer with Excel to be able to run and check my response, but the message of Subscript out of range indicates this type of problem.

The solution: change the indexes as below:

tam = Len(instrument)   ' Sem decrementar
For i = 1 To tam        ' De 1 a tam , em vez de 0 a tam-1