Runtime error '9': Subscript out of Range

KingoftheKassel · #1 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

macropod · #2 06-13-2016, 08:00 PM

It's difficult to work out what your code is supposed to do, since you populate some variables, but then don't use them (e.g. lastRow), and use others that haven't been populated anywhere (e.g. row), plus you have a lot of circumlocution in the code (multiple array creations & population).

Perhaps you want to achieve something like:

Code:

Private Sub CommandButton21_Click()

Dim dataFile As String, dataText As String, StrData As String, Rng As Range, StrErr As String
 
dataFile = Application.GetOpenFilename() 'Select the text file
Open dataFile For Input As #1 'Open the text file as "#1"
  Do Until EOF(1)
    Line Input #1, StrData 'Read in the text file, a line at a time
    Rng = Sheets("ALL-Jul2014").Columns("A").Find(what:=Replace(Mid(StrData, 6, 6), "_", "-"), _
      LookIn:=xlValues, lookat:=xlValues) ' Find the row in Column A
    If Not Rng Is Nothing Then
      Rng.Offset(0, 1).Value = StrData ' Output the data to column B
    Else
      StrErr = StrErr & vbCr & StrData
    End If
  Loop
Close #1
 
Set Rng = Nothing ' Cleanup
If StrErr <> "" Then MsgBox ("No records found for " & StrData) 'Error Report
End Sub

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Conditional Formatting Error (Subscript out of Range))	tinfanide	Excel Programming	1	05-15-2015 08:12 AM
Runtime error 91	waldux	Word VBA	1	03-04-2011 11:25 PM
Runtime error 5487 - Word cannot complete the save to to file permission error	franferns	Word	0	11-25-2009 05:35 AM
Subscript out of range error	KHTAY	Excel	6	03-28-2009 11:18 PM
Mysterious "Subscript out of range" error	rnstewart	Excel	4	12-29-2005 01:04 PM