Your BrandIDList range is defined incorrectly - the definition uses COUNTC rather than COUNTA. If you change that it should be fine.