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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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
95
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,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Where do you dimension the array Ar_Data? What data type will be in it: numbers?
 

krissz

Board Regular
Joined
Feb 21, 2010
Messages
95

ADVERTISEMENT

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,953
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,050
Messages
5,569,891
Members
412,298
Latest member
dietitiann
Top