#1
|
|||
|
|||
Runtime error '9': Subscript out of Range
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 Last edited by macropod; 06-13-2016 at 07:31 PM. Reason: Added code tags & formatting |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
run-time error, subscript out of range |
|
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 |