Use Cell Name To Create Range..when The Vendor Changes

JohnDrew · Jan 2nd 2008

I would like to create a range name when the vendor changes in column "A' and identify the range name as the name in "A".

Name Item QTY
Bill A 2
Charley C 1
Charley d 6
Charley z 7
David x 6
David f 44

I would have range name Bill for B2:C2
Charley B3:c4

Since the data changes, I need to have the program run and delete old name and create a new range with new data.

'
Charley Range b

**Andy Pope** · Jan 2nd 2008

Re: Use Cell Name To Create Range..when The Vendor Changes

Try this,

[vba]
Sub CreateNamedRanges()

Dim lngRow As Long
Dim lngStartRow As Long
Dim strName As String
Dim rngTemp As Range

On Error Resume Next

lngStartRow = 2
For lngRow = 3 To Range("A1").End(xlDown).Row + 1
If Cells(lngRow, 1) <> Cells(lngStartRow, 1) Then
' deal with named range
strName = Cells(lngStartRow, 1)
Set rngTemp = ThisWorkbook.Names(strName).RefersToRange
' delete if already exists
If Not rngTemp Is Nothing Then ThisWorkbook.Names(strName).Delete

ThisWorkbook.Names.Add strName, RefersTo:=Range(Cells(lngStartRow, 1), Cells(lngRow - 1, 3))
lngStartRow = lngRow
End If
Next

End Sub
[/vba]

GrahamB · Jan 2nd 2008

Re: Use Cell Name To Create Range..when The Vendor Changes

Hi Charlie,

I have read your questiona few times and I am struggling to understand it - am I correct in your example Charlie should be b3:c5?? (not b3:c4)

If you example is right can you please give a bit more detail to your question.

Cheers,

GB

Use Cell Name To Create Range..when The Vendor Changes

Participate now!

Share