InStr to do things of file contains string

[Peteor]

Here is what I am currently building:

Sub Macro1()


Dim ER As Long
ER = ThisWorkbook.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Dim Cell As Range
Dim CriteriaRange As Range
Set CriteriaRange = ThisWorkbook.ActiveSheet.Range("A2:A" & ER)
For Each Cell In CriteriaRange
If InStr(1, Cell.Value, "H04C", vbTextCompare) > 0 Then 'Searches for H04C'
File_name = ActiveCell.Value 'Minor issues here... I am unsure if my active cell would be the correct cell...Any input? I think my intention is clear'
Path_Name = ActiveCell.Offset(columnOffset:=5).Value 'Assuming the Active cell is correct, offset 5 and that is where the file is located'
Workbooks.Open Filename:=File_name 'Open the Workbook'
End If
Next Cell

End Sub

I am trying to search for a String in a list of file names on my active worksheet. When it finds that string (for instance, "H04C"), it opens all of the files and does things. Anyone have any input?

 

[Fluff]

Try changing ActiveCell to Cell

 

[Peteor]

Nailed it. Thank you Fluff.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Peteor]

Hey Fluff. Question. I am using this to parse information from lots of sheets and put it onto 1 sheet. I want to copy information starting on row 6 columns A through K for all of the files. I don't think "ActiveSheet.Range("A6:K") will work. How do I specify a limit so I don't copy B1-B5, C1-C5, D1-D5, etc.

 

[Fluff]

As this is a completely different question, please start a new thread.