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

  • 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

  • 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]

    [h4]Cheers
    Andy
    [/h4]

  • 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

    But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!