Deleting everything after "," including "," in an String-CodePudding

Im using a scanner, which fills out an Exel Sheet. Everytime a label which contains a Mac and a Serial number gets scanned it writes it into a cell. I want to delete everything behind (and including ) the "," The Position of the "," is: ,"ss".

This VBA programm should delete :" ss:******************" in every cell, but it doesn´t work, and it gives no error code feedback.

Here is my Code:

    Sub loeschen()

Dim sn As String
Dim mac As String
Dim Cache As String

sn = "***************************ss*:*******************"
If ActiveCell.Value = sn Then
Cache = ActiveCell.Value
mac = Left(Cache, InStr(1, Cache, ",") - 1)

ActiveCell.Value = mac
End If

End Sub

Im gratefull for any help or suggetions.

Current Version:

Sub loeschen()

Dim sn As String
Dim mac As String
Dim Cache As String
Dim T As Boolean
T = True

If Mid(ActiveCell, 28, 2) = "ss" Then
Cache = ActiveCell.Value
mac = Left(Cache, InStr(1, Cache, ",") - 1)

ActiveCell.Value = mac
End If

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs "U:\Desktop\a" & Format(Date, "dd.mm.yyyy") & ".xlsm"

Application.DisplayAlerts = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.ActiveCell) Is Nothing Then loeschen
End Sub

Edit: I had to overthink the process and the programm, i have to make some adjustments for it to be usefull.

It has to get the information from an .txt file
then crop them by an user pressing a macro ( i would like the programm to run an counter "i" which goes through every Cell in the Column A and crops it)

The Data in the Txt File that is important looks like this;

This is my current code:

Sub loeschen()

Dim sn As String
Dim mac As String
Dim Cache As String
Dim i As Integer
Dim j As String


Dim myFile As String, text As String

myFile = "U:\Desktop\Data.txt"

i = 0

While i < 9000
ActiveCell = "A"   i

If Mid(ActiveCell, 28, 2) = "ss" Then
Cache = ActiveCell.Value
mac = Left(Cache, InStr(1, Cache, ",") - 1)

ActiveCell.Value = mac
End If
i = i   1
Wend

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs "U:\Desktop\SHCData" & Format(Date, "dd.mm.yyyy") & ".xlsm"

Application.DisplayAlerts = True

End Sub

This code does not work yet

CodePudding user response：

seems, the problem is in following:

sn = "***************************ss*:*******************" 
If ActiveCell.Value = sn Then

better change on

 If Mid(ActiveCell, 28, 2) = "ss" Then

CodePudding user response：

I created an UDF based on what you want. In your code you have:

mac = Left(Cache, InStr(1, Cache, ",") - 1)

So I guess you want everything before the comma. You may use Split for this:

Code of the UDF:

Public Function GET_LABEL(ByVal rng As Range) As String

If InStr(1, rng.Value, ",") > 0 Then
    GET_LABEL = Split(rng.Value, ",")(0)
Else
    'no comma, get all text
    GET_LABEL = rng.Value
End If


End Function