Hello jp001,
Here is a sample workbook using the Dictionary Object to compare the range names. I have a list of range names in column "M" on "Sheet1". The data is read in from a text file using the data you posted. The first field of the line data is tested using the DSO to see if the rnage name exists. If it does then it will copy the input fields to A1:H1 and go down one row for each match.
Sub CompareRanges()
Dim Data As String
Dim DSO As Object
Dim FileName As String
Dim FilePath As String
Dim N As Integer
Dim R As Long
Dim RngName As Variant
N = FreeFile
FilePath = "C:\Documents and Settings\Admin.ADMINS\My Documents\"
FileName = "Test Data.txt"
NameList = Worksheets("Sheet1").Range("M1:M10").Value
Set DSO = CreateObject("Scripting.Dictionary")
DSO.CompareMode = vbTextCompare
For Each RngName In NameList
If Not DSO.Exists(RngName) Then
DSO.Add RngName, 1
End If
Next RngName
Open FilePath & FileName For Input As #N
Do While Not EOF(N)
Line Input #N, Data
RngName = Split(Data, ",")
If DSO.Exists(RngName(0)) Then
Range("A1").Offset(R, 0).Resize(ColumnSize:=UBound(RngName)) = RngName
R = R + 1
End If
Loop
Close #N
End Sub
Bookmarks