Generate a name on the fly

krissz

Board Regular
Joined
Feb 21, 2010
Messages
92
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
This might set you on the right track:

Code:
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

Board Regular
Joined
Feb 21, 2010
Messages
92
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"
Code:
    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

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
Where do you dimension the array Ar_Data? What data type will be in it: numbers?
 

krissz

Board Regular
Joined
Feb 21, 2010
Messages
92
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
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.
 

Forum statistics

Threads
1,082,552
Messages
5,366,286
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top