"Subscript out of range ( Run time Error: 09)" Problem in My Excel VBA

[sharif007]

Helo guys,

I am having this "Subscript out of range ( Run time Error: 09)" error whenever trying to execute the below codes in my excel VBA editor:
=================================
Example: 1
Sub ExtractText()

Worksheets("SplitCell").Activate
Dim splitText As Variant
splitText = Split(Worksheets("SplitCell").Cells(2, 1).Value, """")

Worksheets("SplitCell").Cells(5, 2).Value = Trim(splitText(1))

End Sub
===========================================
Example: 2
Sub names2()

Dim namesRng As Range, cell As Range
Dim arr As Variant
Dim fName As String, mName As String, lName As String

Set namesRng = ActiveSheet.Range("A2:A10") '<== here set the actual range of "Data", header excluded

For Each cell In namesRng.SpecialCells(xlCellTypeConstants, xlTextValues)

arr = Split(cell.Value, ",")
lName = Trim(arr(0))
If UBound(arr) = 2 Then lName = lName & " " & Trim(arr(2))
arr = Split(Trim(arr(1))) (Showing the same error here aswell)
fName = Trim(arr(0))
mName = ""
If UBound(arr) = 1 Then mName = Trim(arr(1))

cell.Offset(, 1).Resize(, 3) = Array(fName, mName, lName)

Next cell

End Sub

==========================================
Can anybody help me to fix this out?
During debug it shows that problem is happening here -> Worksheets("SplitCell").Cells(5, 2).Value = Trim(splitText(1)) ( Example 1) And arr = Split(Trim(arr(1))) ( Examle 2)
I tried to solve this error in every possible way that I found in the web. but unfortunately could not solve.

your quick response and help will be highly appreciated. Thanks in Advance!!

Regards
Sharif 007

 

[rollis13]

What sort of data are you treating in A2:A10 ?

 

[6StringJazzer]

In both cases you are referring to element 1 of an array that was the result of a call to Split.

splitText = Split(Worksheets("SplitCell").Cells(2, 1).Value, """")
Worksheets("SplitCell").Cells(5, 2).Value = Trim(splitText(1))

What is the value of Worksheets("SplitCell").Cells(2, 1).Value when this error occurs? If it does not have at least two " characters then your reference to splitText(1) will raise this error.

arr = Split(cell.Value, ",")
lName = Trim(arr(0))
If UBound(arr) = 2 Then lName = lName & " " & Trim(arr(2))
arr = Split(Trim(arr(1))) ' (Showing the same error here aswell)

What is the value of cell.Value when Split is called? If there are not at least two "," characters in cell.Value, a reference to arr(1) will raise that error.

 

[sharif007]

In both cases you are referring to element 1 of an array that was the result of a call to Split.

splitText = Split(Worksheets("SplitCell").Cells(2, 1).Value, """")
Worksheets("SplitCell").Cells(5, 2).Value = Trim(splitText(1))

What is the value of Worksheets("SplitCell").Cells(2, 1).Value when this error occurs? If it does not have at least two " characters then your reference to splitText(1) will raise this error.

arr = Split(cell.Value, ",")
lName = Trim(arr(0))
If UBound(arr) = 2 Then lName = lName & " " & Trim(arr(2))
arr = Split(Trim(arr(1))) ' (Showing the same error here aswell)

What is the value of cell.Value when Split is called? If there are not at least two "," characters in cell.Value, a reference to arr(1) will raise that error.

the value was - asasd "4567", 3!@#$"ttt" assad "8999" . Please give a solution here.

 

[sharif007]

yes, the split function does so (returns outputs to an array). so what is the solution to the error?

the value was - asasd "4567", 3!@#$"ttt" assad "8999" . Please give a solution here

 

[6StringJazzer]

I assume your value was only for the first part of my question.

When I replicate your Split, I do not get an error.

$scratch.xlsm
	A
1	- asasd "4567", 3!@#$"ttt" assad "8999"
Split VBA problem

Public Sub spliterror()

   Dim splitText As Variant
  
   splitText = Split([A1], """")
   Debug.Print splitText(1)

End Sub

Immediate window output:

4567