Microsoft Office Forums - View Single Post - Runtime error '9': Subscript out of Range

KingoftheKassel · 06-13-2016, 08:25 AM

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 11-0023_147_19931113156223_CAN7W23 ) 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:

Code:

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