VBA to ignore certain MAC Address values but process all others

[stlouis]

In gathering MAC addresses from multiple sources on the network, I condense them to a single excel file using a script several members helped me make, and it has worked flawlessly for several years! Historically, the MAC Addresses are coming in like aaaa.aaaa.aaaa.aaaa others as aa.aa.aa.aa.aa.aa and others as aa-aa-aa-aa-aa-aa. The script attached works fine to strip the separators away and reformat them to one standard, specifically, AA-AA-AA-AA-AA-AA. Now the monkey wrench comes in with such addresses as all zeros 00-00-00-00-00-00, bound by parenthesis (aa:aa:aa:aa:aa:aa), "Dip free MAC", and "NONE" I have solved the bound by parenthesis problem, but can't figure out how to deal with the "Dip free MAC", all zeros, and "none" issue (I want them to skip being processed and just default to "N/A". But, in doing so, the script correctly recognizes them as having invalid MAC hex characters and erroring out.

So I thought I could do is precede the script with a replace line of something like replace ("dip free MAC", "N/A") and replace ("000000000000", "N/A") and replace ("none", "N/A") but then the script continues to process N/A as invalid also.

So I thought to create a goto line that upon finding one of those problem situations, goto an ignore section at the end of the script to change the cell to N/A, thinking that since it was at the end, the rest of the processing would be skipped and the "N/A" value I desired would be allowed to skip out of the MAC address processing. And that was not correct either.

Can anyone please assist me with this?

Private Sub Worksheet_Change(ByVal Target As Range)
 'assumes just 1 column for the MAC address entries
 'but could be adapted for more

'change these constants to define your worksheet
   Const maEntryColumn = "E" ' MAC addresses entered into this column
   Const maFirstRow = 2 ' first row that could have MAC address in it
 
   Const sepChar = ":"  ' to put between each pair of address parts
   Const macLength = 12 ' # characters in MAC address
   Const macValidCharacters = "ABCDEF01234567890"
 'working variables
   Dim LC As Integer ' loop counter
   Dim tempMACAddress As String
       If Target.Cells.Count = Application.CountBlank(Target) _
        Or Target.Column <> Range(maEntryColumn & 1).Column _
        Or Target.Row < maFirstRow Then
        Exit Sub ' no work to do
   End If
   Dim Cl As Range
   For Each Cl In Intersect(Target, Range("E:E"))
      'in case they typed some separator character in as part of the entry
        tempMACAddress = Replace(Cl.Text, ":", "")
        tempMACAddress = Replace(tempMACAddress, "-", "")
        tempMACAddress = Replace(tempMACAddress, " ", "")
        tempMACAddress = UCase(tempMACAddress)
      'check for special case: entry is all digits, no alpha and
      'is less than 12 characters.  Means we probably dropped leading zeros
      'put them back
      If Len(tempMACAddress) < macLength Then
        For LC = 1 To Len(tempMACAddress)
          If Mid(tempMACAddress, LC, 1) < "0" _
           Or Mid(tempMACAddress, LC, 1) > "9" Then
            Exit For ' exit early
          End If
        Next
        If LC = Len(tempMACAddress) + 1 Then
          'were all digits, add leading zeros
          tempMACAddress = _
           String(macLength - Len(tempMACAddress), "0") & tempMACAddress
        End If
      End If
      If Len(tempMACAddress) <> macLength Then
        MsgBox "The entry does not conform to MAC Address length of " & macLength, _
         vbOKOnly + vbExclamation, "Invalid Entry"
        Exit Sub
      End If
      'validate as all valid characters
      For LC = 1 To Len(tempMACAddress)
        If InStr(macValidCharacters, Mid(tempMACAddress, LC, 1)) = 0 Then
          MsgBox "The entry has invalid MAC Address character: " & Mid(tempMACAddress, LC, 1), _
           vbOKOnly + vbExclamation, "Invalid Entry"
          Exit Sub
        End If
      Next
 
      'looks valid, format it up
      Application.EnableEvents = False ' to prevent re-entry to this routine
      Cl = Left(tempMACAddress, 2) & sepChar _
       & Mid(tempMACAddress, 3, 2) & sepChar _
       & Mid(tempMACAddress, 5, 2) & sepChar _
       & Mid(tempMACAddress, 7, 2) & sepChar _
       & Mid(tempMACAddress, 9, 2) & sepChar _
       & Right(tempMACAddress, 2)
   Next Cl
   Application.EnableEvents = True
 End Sub

 

[myall_blues]

You could probably do something with a 'Select case' statement to bypass the invalid entries. For example:

'
' Code up to this point
'
For Each Cl In Intersect(Target, Range("E:E"))
    Select Case Cl
        Case "Dip free MAC"
            ' Do nothing
        Case "NONE"
            ' Do nothing
        Case Else
            ' Everything must be OK - process rest of code
            '
        End Select
    Next Cl

Regards

Murray