Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- Mobile
Hi Guys,
I want to create a code to split the content of a cell into 3 colurmns but it does not work out. Please help to make it through.
What the code should do is to convert the those numbers in the left to MAC address and put it into column B, and those numbers after "X=" and "Y=" to column C and D respectively.
Here is my unfinished code
I want to create a code to split the content of a cell into 3 colurmns but it does not work out. Please help to make it through.
What the code should do is to convert the those numbers in the left to MAC address and put it into column B, and those numbers after "X=" and "Y=" to column C and D respectively.
Here is my unfinished code
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myText As String
Dim macText As String
Dim c As Range
Dim lrow As Long
Dim EntryMac As Long
Dim EntryX As Long
Dim EntryY As Long
lrow = Range("A1").End(xlDown).Row
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo ErrHandler:
If Len(Target) < 12 Then Exit Sub 'check for correct length
If Target.Column <> 1 And Target.Row = 1 Then Exit Sub 'only Column A and Row 2 downwards
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("A2:A" & lrow)) Is Nothing Then
For Each c In Range("b2:b" & lrow)
macText = Left(Target.Value, 12)
myText = Left(macText.Value, 2) _
& "-" & Mid(macText.Value, 3, 2) _
& "-" & Mid(macText.Value, 5, 2) _
& "-" & Mid(macText.Value, 7, 2) _
& "-" & Mid(macText.Value, 9, 2) _
& "-" & Right(macText.Value, 2)
EntryMac = Left(Target, 12)
EntryX = InStr(Target, "X=" - InStr(Target, "Y="))
EntryY = InStr(Target, "Y=")
c.Value = UCase(myText)
mcell.Value = macText
Next c
Application.EnableEvents = True
End Sub