Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 06-13-2016, 08:25 AM
KingoftheKassel KingoftheKassel is offline Runtime error '9': Subscript out of Range Windows 7 64bit Runtime error '9': Subscript out of Range Office 2010 64bit
Novice
Runtime error '9': Subscript out of Range
 
Join Date: Jul 2015
Posts: 5
KingoftheKassel is on a distinguished road
Default 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
Reply With Quote
 

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 '9': Subscript out of Range 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
Runtime error '9': Subscript out of Range Subscript out of range error KHTAY Excel 6 03-28-2009 11:18 PM
Runtime error '9': Subscript out of Range Mysterious "Subscript out of range" error rnstewart Excel 4 12-29-2005 01:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:53 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft