Generate a name on the fly

krissz

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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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.
 
Upvote 0
Where do you dimension the array Ar_Data? What data type will be in it: numbers?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top