Runtime error '9': Subscript out of Range

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