Hello,

I am pretty new to VBA (my third mini-VBA code project ever...pretty excited, actually and this is by far the most complex thing I tried to tackle) I am running into issue with execution of my code and I would like to solicit some suggestions.

Objective: to create multiple named ranges based on index data point pertinent to that specific data set. Then set that index data point as the name of that specific range.

I manually hardcoded an index in column A which contains mostly blank cells and a few data points which I would like to use as names for a range of values to the right of the index. Here is the approach I have taken:

1. loop over the index
2. keep looping if blank
3. end the loop if come to a value "end"
4. if the cell is neither of those then take cell value and use it as a name for a range that should span 7 columns to the left and 40 rows down

Here is the code I have so far, it doesnt execute and I am just too "green" to pick out the issue. Went at it for a few hours now trying a few different things, but no success. Could some one please offer some tips or suggestions?

I am also attaching a sample workbook I am working on...I need this because eventually I will have 30-40 data tables (which might vary in length of rows, but I'll figure that one out later) which I will need to create named ranges for.

Thank you for your time and insights

Code: 

 
    Sub RAINgMaker()

    Dim index, lastrow As Range
    Dim cellvalue As String
 
 Set index = Sheets("DEALER").Range("A1:A999")
    lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    With index
        For i = 1 To lastrow
            If Selection.Cells.Value = 0 Then
            ' do nothing and go to the next loop
            ElseIf cell.Value = "End" Then
            Exit For 'end this loop and move to the next whole loop that look at the next tab, ending procedure for this loop
            Else
                For Each cellvalue In index
                     Selection.Cells.Value = cellvalue
                'get the value of the index cell
                Range(ActiveCell, ActiveCell.Offset(7, 40)).Name = cellvalue
                'create a range that is to the side of the cell, 7 cells to the right and 40 cells down ...and name it with "cellvalue"

        End If
       Next
    End With


End Sub