Generate a name on the fly

[krissz]

I have an excel 2003 spreadsheet that contains test results from multiple tests on multiple products. Each individual result is in a named cell. The cell name is in the format:
"result_x_Ty"
where x = Product No
y = Test No
Depending on other criteria, I then need to copy selected results into a report. This I do by filling an array with the necessary results:
result_ar(1,x) = [result_x_Ty]
Is there a way that I can achieve this with VBA ?

I have tried & googled with no success.

 

[wigi]

This might set you on the right track:

Sub f()

    Dim x As Long, i As Long, j As Long, r As Range
    
    For i = 1 To 10    'loop over product numbers

        For j = 1 To 5    'loop over test numbers

            x = x + 1
            
            Set r = Range("result_" & i & "_T" & j)
            
            If Not r Is Nothing Then result_ar(1, x) = r.Value

        Next

    Next

End Sub

 

[krissz]

Wigi
Thanks for your help, but I keep getting a 'Type Mismatch' error on Ar_Data(1, 1) = L_Range.Value
The Debug gives me 20802 which is correct; the content of the cell named "Serial_No_1"

    Dim Meter_Pr As Variant
    Dim Meter_Cnt As Integer
    Dim Ar_Data As Variant      
    Dim L_Range As Range

If Meter_Pr(1, Meter_Cnt) = "Yes" Then
            
    '   Copy Data to Archive Sheet
    Set L_Range = Range("Serial_No_" & Meter_Cnt)
Debug.Print L_Range.Value

   If Not L_Range Is Nothing Then Ar_Data(1, 1) = L_Range.Value
            
    Ar_Data(1, 2) = [engineer_test]
    Ar_Data(1, 3) = etc

I have obviously done something wrong, but what ?
I have tried square brackets; also without Value.

 

[wigi]

Where do you dimension the array Ar_Data? What data type will be in it: numbers?

 

[krissz]

I do not explicitly dimension it, but it is a single row and at present contains 9 items. I use variant as the content is mixed; string, date, time or numeric.

 

[wigi]

Try

Dim Ar_Data() As Variant

'Redim Preserve' the array whenever you add items in the array (or choose a number of items in the array before you start filling it.

 

[krissz]

Wigi
Thank you for your advice. It now works.