For loop to uniquely name each non empty cell in a range

Question

The code below names the last cell in the range as opposed to each cell in the range.

I am trying to run this loop so that starting from cell A1, any non empty cells are named "Guidance1", "Guidance2", and so on.

Sub GiveAllCellsNames()

    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim R As Range
    Dim NameX As String

    Static I As Long
    I = I + 1
 
    NameX = "Guidance" & I

    For Each R In Range("A1:A390").Cells
        If R.Value <> "" Then
            With R
                wb.Names.Add NameX, RefersTo:=R
            End With
        End If
    Next R

End Sub

I tried this loop without the "with statement" on the "R" range variable and get the same result.

Answer 1

A named range can be added using the Range object's name property.

Change

 wb.Names.Add NameX, RefersTo:=R

To

 R.Name = NameX

I need to be incremented and the name should be updated inside the loop.

Sub GiveAllCellsNames()

    Dim wb As Workbook

    Set wb = ActiveWorkbook

    Dim R As Range

    Dim NameX As String

    Static I As Long

    For Each R In Range("A1:A390").Cells

        If R.Value <> "" Then
        
            I = I + 1
            NameX = "Guidance" & I
            
            With R

                wb.Names.Add NameX, RefersTo:=R

            End With

        End If

    Next R

End Sub