Dear All,

I am trying to dynamically access certain values from a sheet (store) from a dynamic ranges.

I have a store into which raw materials are recorded with their expiry dates.
Each material could have several different batches with different expiry dates. My goal is to comb all these to ensure that for each batch in the store the products are not expired or are not about to expire.

Because i have like 8 raw materials each which has an indeterminate number of active batches. Writing static code looks like a job which can take as many as three days. Because of my consistent naming of ranges (A_Expiry, A_Batch, B_Expiry, B_Batch) I decided to access these named ranges using a variable
Dim IngName as Variant
IngName = ThisWorkbook.Names("Name_Material").RefersToRange
Dim ExpDate As Variant, Q As Variant, Expiry as String
For Each mem In IngName
    Expiry = mem & "_Expiry" ' translates to e.g "A_Expiry"
    Qtty = mem & "_Qtty"
    ExpDate =ThisWorkbook.Names(Expiry).RefersToRange 'ExpDate should recieve a range array<----------
    Q=ThisWorkbook.Names(Qtty).RefersToRange
    'other code here
next mem
NOte ExpDate should recieve an array.
Yes and the names of the range are valid and the variables translate correctly to the names of ranges
Any body with any idea. Line with error is in code in bold