0

I am having trouble with outputting values from my split code. It works in the if section of my code but not my else section. I keep getting

Runtime Error 9 - Subscript Out of Range

when I run.

Dim SR As Integer
Dim z As Integer
Dim lastrow As Long
Dim str1() As String
Dim str2() As String
Dim avarsplit As Variant
Dim xRg As Range, yRg As Range, yRgD As Range, yRgE As Range, yRgF As Range, yRgG As Range, yRgH As Range, yRgI As Range
    SR = 2
    z = 1

Do While Worksheets("AppendedData").Cells(SR, 13) <> ""
    If Worksheets("AppendedData").Cells(SR, 13) = 1 And Worksheets("AppendedData").Cells(SR, 12) = 0 Then
        avarsplit = Split(Worksheets("AppendedData").Cells(SR, 1).Value, " - ")
        splitValues = Split(Worksheets("AppendedData").Cells(SR, 1).Value)

        Worksheets("AppendedData").Cells(SR, 27).Value = splitValues(0)
        Worksheets("AppendedData").Cells(SR, 26).Value = splitValues(1)

    z = z + 1
    SR = SR + 1
Else:
    Worksheets("AppendedData").Select
    Worksheets("AppendedData").Cells(SR, 15).FormulaR1C1 = "=3"
    Worksheets("AppendedData").Cells(SR, 29).Value = "Delete"

    avarsplit = Split(Worksheets("AppendedData").Cells(SR, 1).Value, " - ")
    splitValues = Split(Worksheets("AppendedData").Cells(SR, 1).Value)

    Worksheets("AppendedData").Cells(SR, 27).Value = splitValues(1) ' ERROR OCCURS HERE!

        SR = SR + 1
    End If
Loop

Thank you your comments made me realise there was an error in my test data rather than my code.
However I am now looking to find out what value splitValues(1) would take if the split did not work. i.e. if the cell was "TEST" rather than "TEST - 123" as expected. Any ideas?
As I am looking to make an if statement for what to do when the split fails

Community
  • 1
  • 1
O.r.A.j.C
  • 3
  • 4
  • What line is your code breaking on? – freginold Nov 21 '17 at 17:40
  • 3
    Most likely there is no space on which to split the values and as such splitvalues only has one item in the array. Therefor there is no `splitValues(1)` only `splitValues(0)` – Scott Craner Nov 21 '17 at 17:43
  • before you refer an index, ensure its availability by using `Ubound(splitValues)` – cyboashu Nov 21 '17 at 17:51
  • It looks like you are not including the second argument in the `Split` function when setting `splitValues` like you are when you are setting `avarsplit` – braX Nov 21 '17 at 17:59
  • 2
    @braX it defaults to space when not included. see: https://www.techonthenet.com/excel/formulas/split.php – Scott Craner Nov 21 '17 at 18:02
  • Try using some error handling, refer to [this answer and see a function with Split and errorHandling](https://stackoverflow.com/a/46856103/7690982) – danieltakeshi Nov 21 '17 at 18:48
  • Thank you your comments made me realise there was an error in my test data rather than my code. However I am now looking to find out what value splitValues(1) would take if the split did not work. i.e. if the cell was "TEST" rather than "TEST - 123" as expected. Any ideas? As I am looking to make an if statement for what to do when the split fails – O.r.A.j.C Nov 22 '17 at 10:51

0 Answers0