Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro that changes name field (2003)

    I have a spreadsheet that is sorted on NAME field. I would like to run a macro on this field that if the name is repeated more than once - say Mary Brown is in there at least 2 times - to replace Mary Brown (as many times as there is Mary Brown) with Benefit Administrator. If the name is in there once, then let it stay the name. I hope this is clear. Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that changes name field (2003)

    Try this (make a backup copy of your workbook first):

    Sub ReplaceDupNames()
    Dim lngRow As Long
    Dim lngEndRow As Long
    Dim lngKeep As Long
    Const lngStartRow = 2
    Const strCol = "A"
    lngEndRow = Range(strCol & "65536").End(xlUp).Row
    lngKeep = lngStartRow
    For lngRow = lngStartRow + 1 To lngEndRow + 1
    If Not (Range(strCol & lngRow) = Range(strCol & (lngRow - 1))) Then
    If lngRow > lngKeep + 1 Then
    Range(strCol & lngKeep & ":" & _
    strCol & (lngRow - 1)) = "Benefit administrator"
    End If
    lngKeep = lngRow
    End If
    Next lngRow
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •