Quote:
Originally Posted by gmaxey
batman1,
Interesting approach and while I am close to making it work, the actual file is much larger and more complicated than my simple example.
Two things:
1. There is not always a match. So when I run your code, Count and Ubound(result) are not the same.
|
They don't have to be the same. I understand that for each row in Sheet2 you have to look for Name in Sheet1. At the very beginning the code doesn't know how many matches there are, so I declare result with the maximum row - as many as the row in Sheet2
Code:
ReDim result(1 To UBound(data, 1), 1 To 5)
So for example Sheet2 has 100 rows then Ubound(result, 1) = UBound(data, 1) = 100, and there are 5 matches then count = 5 - Ubound(result, 1) <> count.
But since there are UBound(data, 1) rows in Sheet2 then there are maximum count = UBound(data, 1) matches:
Always count <= UBound(data, 1) = UBound(result, 1)
Quote:
2. So, I think that is why I get error about subscript out of range with Resize(count, 5).
|
I don't understand. count <= UBound(result, 1), so it can't be "subscript out of range error"
Since you didn't attach a file, I created the file myself and I'm testing for data in such structures. For the future, attach the file.