setting a named range based on offset cell text info

Question

I have a Sheet named "Sales". This sheet has Column A2:A1500 with Item Names. Each of the A2 through A1500 rows has sales info on the adjoining columns per item. I have a Web API which updates the item's sales information per row and it's adjoining column with new data( always overwriting same row).

I am trying to create a code to create named ranges based on the adjoining cell's text and if a new item name is created to create a named range for the new item added.

1st. I would want to create named ranges out of each Item name per row. meaning Cell A2 text will be "ItemName1" Range, A3 text will be "ItemName2" range. this function i will need to iterate through the whole column A last row.

2nd. if the Web API created a new Itemname within column A, the api is set to create the new item name at the last known row of column A.

This is what i have so far. but i thought of the variable of the new item added and i do not have a solution for that.

Sub NamedRanges()
Dim rngCell As Range
Dim intLstRow As Integer

intLstRow = ActiveSheet.UsedRange.Rows.Count

For Each rngCell In Range("B2:B" & intLstRow)
    ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -1).Value, RefersTo:=Range("Sales!$B$" & rngCell.Row)
Next
End Sub

I am currently getting an

application or object defined error

at ActiveWorkbook.Names.Add Name:=.

I do see my first cell name on RefersTo:=Range("Sales"!$A$" & rngCell.Row).

Thanks In advance.

ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, _ RefersTo:=Range("Sales!$A$" & rngCell.Row) — QHarr
Will name first row but then give me "application or object defined error" to your question the column A or B has the same item name. — Xango

QHarr QHarr · Accepted Answer · 2017-12-21T17:34:42

Try the following. It uses correct syntax for concatenating the name for the named range and uses a different method for finding the last row. Your method using UsedRange can give unexpected results. I also changed name for last row to LstRow as you don't need to use Hungarian notation in VBA, also put as Long to avoid overflow.

Option Explicit

Sub NamedRanges()

Dim rngCell As Range
Dim LstRow As Long

With ActiveSheet

    LstRow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With


For Each rngCell In Range("B2:B" & LstRow)
    ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -1).Value, RefersTo:=Range("Sales!$B$" & rngCell.Row)
Next

End Sub

setting a named range based on offset cell text info

1 Answers