Create a variable number of ranges and give it a variable name in VBA

Question

I have a table with model names on column 1 and quantity on column 2. The length of this table changes every time. Also, some models can be listed without a quantity (the correspondent cell in Col2 is empty). I would like to create a VARIABLE number of ranges depending on how many non-zero values are in my column 2. Then, I would like to name those ranges using the name on the model corresponding to such range + “Qty” (e.g. if the first non-zero value is the one corresponding to the model1, I'd like to name the range Model1Qty. If the non-zero values found are 2, 2 Dim ranges need to be created, each based on the name of the corresponding model, etc). If the pic of my table goes through, in my example 2 ranges should be created: Model1Qty (range(“B2”)) and Model3Qty (range(“B4”)).

This is the beginning of my code:

Sub CreatingRng()
   Dim N1 As Integer
   Dim R As Range
     Set R = Range("A1").CurrentRegion
     For N1 = (1 + R.Row) To (R.Rows.Count + R.Row)
       Cells(N1, R.Columns(2).Column).Select
       If IsNumeric(Cells(N1, R.Columns(2).Column)) And _
        Cells(N1, R.Columns(2).Column) > 0 Then
           ‘create a range with Cells(N1, R.Columns(2).Column), named after the corresponding model name
       End If
     Next N1
End Sub

And an example: Example

Thanks @peter! The code works but I can't see the value of the name and I can't use it into a function: (note I changed the model names in the table to mod1/etc): if I add: Dim VarValue1 As Variant 'added at the top of code '(…) original code Next N1 VarValue1 = Application.Evaluate("mod1Qty") ''it shows the correct value on immediate window Debug.Print mod1Qty ' doesn't show anything in the Immediate window Range("b5") = mod1Qty + mod3Qty 'writes zero on the cell End Sub Thanks again for your patience! — M_Idk392845

tlemaster tlemaster · Accepted Answer · 2017-01-15T03:43:34

Give this a try:

Sub CreatingRng()
    Dim N1 As Integer
    Dim R As Range

    Set R = Range("A1").CurrentRegion
    For N1 = (1 + R.Row) To (R.Rows.Count + R.Row)
        Cells(N1, R.Columns(2).Column).Select
        If IsNumeric(Cells(N1, R.Columns(2).Column)) And Cells(N1, R.Columns(2).Column) > 0 Then
            ActiveWorkbook.Names.Add Name:=Selection.Offset(0, -1).Value & "Qty", RefersToR1C1:=Selection
        End If
    Next N1
End Sub

Create a variable number of ranges and give it a variable name in VBA

1 Answers