Runtime error '9': Subscript out of Range

[kingofthekassel]

Hey guys I have had a friend helping me put together this code. Basically I have .txt files I have information on and I need to add that information to an existing database by matching ID numbers however the ID number in the .txt is embedded in the middle of a larger ID number. (i.e. on excel I have 5123-147 and in the .txt I have 0390239-0023-147-DBU) I think the code will work I just need some help with this runtime error. Here's the code. It happens in the first 'for' loop on the searchString(i) line. I appreciate the help, guys:

Private Sub CommandButton21_Click()


Dim dataFile As String, dataText As String, dataTextLine As String, row As Long, lastRow As Long
Dim flightString As String, flightStringArray() As String, searchString() As String
Dim dataArray() As String, lineNumber As Long, i As Long, j As Long, cellValues() As String
Dim column As Long, reformatComponent() As String, found As Range

dataFile = Application.GetOpenFilename() 'Select the text file
Open dataFile For Input As #1 'Open the text file as "#1"
lineNumber = 0 'Start at the first line, indexed beggining at 0
Do Until EOF(1)
Line Input #1, dataTextLine 'Read in the text file, a line at a time
dataText = dataText & dataTextLine & ";"
lineNumber = lineNumber + 1 'Increment line number
Loop
Close #1

dataArray() = Split(dataText, ";")

For i = 0 To lineNumber
reformatComponent() = Split(Mid(dataArray(i), 6, 6), "_")
searchString(i) = reformatComponent(0) & "-" & reformatComponent(1)
Next i

With Worksheets("ALL-Jul2014")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).row 'Determine how many rows the worksheet has
End With

For j = 0 To lineNumber
found = Sheets("ALL-Jul2014").Columns("A").Find(what:=searchString(j), LookIn:=xlValues, lookat:=xlValues)
cellValues() = Split(dataArray(i), vbTab) 'Take the .txt info and put it in an array
For column = 81 To 210
Worksheets("ALL-Jul2014").Cells(row, column).Value = cellValues(column - 81) 'Put each array element into the correct cell
Next column
Next j

End Sub

 

[wideboydixon]

This line:

Split(Mid(dataArray(i), 6, 6), "_")

Is only returning an array with 1 value because there are no "_" characters in Mid(dataArray(i), 6, 6). Should this be "-" instead of "_" ?

WBD

 

[kingofthekassel]

My apologies. In my original post, it should be 0390239-0023_147_DBU.

 

[wideboydixon]

Mid$("0390239-0023_147_DBU", 6, 6) = "39-002"
Mid$("0390239-0023_147_DBU", 11, 6) = "23_147"

Perhaps you need 11 and not 6 as the starting character.

WBD

 

[kingofthekassel]

Thank you for your help with this! Unfortunately, that isn't what is wrong. I can't post the data online but starting character in the real data is 6. I should have given a more accurate example. Here is the EXACT format of the data:


In excel, ID Number:
5123_147

In text, ID Number:
11-0023_147_19931113156223_CAN7W23

 

[wideboydixon]

When the code errors, take a look at the value of dataArray(i). The easiest way is to use Ctrl+G and type the following in the immediate window:

? dataArray(i)
? Mid$(dataArray(i), 6, 6)

I'm sure you'll see that there are missing "_" in the value when it fails.

WBD

 

[kingofthekassel]

This is what came up:

23_147

What does this mean? I am sorry for the simple questions, I am finding I am not as good with VBA as with other languages.

 

[wideboydixon]

Haha! I've been looking at the wrong thing all along. You need the following:

ReDim searchString(lineNumber)
For i = 0 To lineNumber
reformatComponent() = Split(Mid(dataArray(i), 6, 6), "_")
searchString(i) = reformatComponent(0) & "-" & reformatComponent(1)
Next i

WBD

 

[kingofthekassel]

Hmmm now it says that that line is the error. (same error, subscript out of range)

 

[kingofthekassel]

I closed and reopened the file and your line is no longer a problem. But I still get the error on the searchString line