Split and Find or Left and Find?

[p9326]

I have a 2 worksheet 1 is for data base with 3 command buttons, 1 sheet for barcode scanner data transferring to excel with date and time stamp are included.

Could someone could help me to split or left and find functions to remove the date and time stamps on A2 and B2 Column when I click my consolidated button.

Here is the code in consolidated button.

Sub Sample()
    Dim ws As Worksheet
    Dim wsCon As Worksheet
    Dim lr As Long: lr = 2
    Dim wsLR As Long
   
    '~~> This is your Consolidated worksheet
    Set wsCon = ThisWorkbook.Sheets("Consolidated")
    wsCon.AutoFilterMode = False
   
    '~~> Clear the contents for input
    wsCon.Range("A2:C" & wsCon.Rows.Count).ClearContents
   
    '~~> Copy the data in to the Consolidated worksheet from other sheets
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsCon.Name Then
            wsLR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
           
            ws.Range("A2:C" & wsLR).Copy wsCon.Range("A" & lr)
           
            lr = wsCon.Range("A" & wsCon.Rows.Count).End(xlUp).Row + 1
        End If
    Next ws
   
    With wsCon
        '~~> Remove duplicates
        .Range("A1:C" & lr).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
        '~~> Apply filter in row 1
        .Range("A1:C1").AutoFilter
    End With
End Sub

I attached the Picture for much

 

[Anthony47]

Try adding this code after With wsCon:

    With wsCon                      '
'>>> Added code
        For Each myC In .Range("A1:B" & lr)
            stYear = InStr(1, myC.Value, CStr(cYear - 1), vbTextCompare)                       '?
            If stYear = 0 Then stYear = InStr(1, myC.Value, CStr(cYear - 0), vbTextCompare)    '?
            If stYear = 0 Then stYear = InStr(1, myC.Value, CStr(cYear + 1), vbTextCompare)    '?
            If stYear > 0 Then
                myC.Value = Trim(Left(myC.Value, stYear - 1))
            End If
        Next myC
'<<< End
        '~~> Remove duplicates       'Your code

This will search for strings like 2021/, 2022/, 2023/, ie thisYear-1, thisYear, thisYear+1; if this approach is not enough we either could add lines similar to those
marked ? or develop different algorithm for a safe identification of the date

 

[p9326]

Try adding this code after With wsCon:

    With wsCon                      '
'>>> Added code
        For Each myC In .Range("A1:B" & lr)
            stYear = InStr(1, myC.Value, CStr(cYear - 1), vbTextCompare)                       '?
            If stYear = 0 Then stYear = InStr(1, myC.Value, CStr(cYear - 0), vbTextCompare)    '?
            If stYear = 0 Then stYear = InStr(1, myC.Value, CStr(cYear + 1), vbTextCompare)    '?
            If stYear > 0 Then
                myC.Value = Trim(Left(myC.Value, stYear - 1))
            End If
        Next myC
'<<< End
        '~~> Remove duplicates       'Your code

This will search for strings like 2021/, 2022/, 2023/, ie thisYear-1, thisYear, thisYear+1; if this approach is not enough we either could add lines similar to those
marked ? or develop different algorithm for a safe identification of the date

I get some error on myC.

 

[Anthony47]

You know what Option Explicit does... so you need to declare variable myC; add this to the other Dims:

Dim myC as Range

 

[p9326]

You know what Option Explicit does... so you need to declare variable myC; add this to the other Dims:

Dim myC as Range

stYear and CYear also define are this also for Range?


Dim ws As Worksheet
Dim wsCon As Worksheet
Dim lr As Long: lr = 2
Dim wsLR As Long
Dim myC As Range



all I want to remove date and time stamp (2022/09/07 14:30:44) for column A&B when I hit the command button.

 

[p9326]

stYear and CYear also define are this also for Range?


Dim ws As Worksheet
Dim wsCon As Worksheet
Dim lr As Long: lr = 2
Dim wsLR As Long
Dim myC As Range



all I want to remove date and time stamp (2022/09/07 14:30:44) for column A&B when I hit the command button.

Or should I make a new command button to remove those dates and time stamp?

 

[Anthony47]

Hummm...
I tried to integrate my code within your macro, but that made things more complicated that I expected.
So:
-return to your original macro
-insert in your macro this line between Next ws and With wsCon

    Next ws
    Call CleanDate(lr)              '<<<<   ADDED LINE
    With wsCon

-add this new subroutine to the same vba module

Sub CleanDate(ByVal Last As Long)
Dim myC As Range, cYear As Long, stYear As Long
'
cYear = Year(Date)
With Sheets("Consolidated")
        For Each myC In .Range("A1:B" & Last)
            stYear = InStr(1, myC.Value, CStr(cYear - 1), vbTextCompare)
            If stYear = 0 Then stYear = InStr(1, myC.Value, CStr(cYear - 0), vbTextCompare)
            If stYear = 0 Then stYear = InStr(1, myC.Value, CStr(cYear + 1), vbTextCompare)
            If stYear > 0 Then
                myC.Value = Trim(Left(myC.Value, stYear - 1))
            End If
        Next myC
End With
End Sub

This should be less error prone that the previous suggestion (message #2)

 

[p9326]

Hummm...
I tried to integrate my code within your macro, but that made things more complicated that I expected.
So:
-return to your original macro
-insert in your macro this line between Next ws and With wsCon

    Next ws
    Call CleanDate(lr)              '<<<<   ADDED LINE
    With wsCon

-add this new subroutine to the same vba module

Sub CleanDate(ByVal Last As Long)
Dim myC As Range, cYear As Long, stYear As Long
'
cYear = Year(Date)
With Sheets("Consolidated")
        For Each myC In .Range("A1:B" & Last)
            stYear = InStr(1, myC.Value, CStr(cYear - 1), vbTextCompare)
            If stYear = 0 Then stYear = InStr(1, myC.Value, CStr(cYear - 0), vbTextCompare)
            If stYear = 0 Then stYear = InStr(1, myC.Value, CStr(cYear + 1), vbTextCompare)
            If stYear > 0 Then
                myC.Value = Trim(Left(myC.Value, stYear - 1))
            End If
        Next myC
End With
End Sub

This should be less error prone that the previous suggestion (message #2)

@Anthony47 superb, Working... yahooo.. your the man..

 

[Anthony47]

Thank you for the feedback

 

[p9326]

Thank you for the feedback

Thank you again.. Now I will make a userform with search buttons showing only the remaining IN item so I can trace what item are still on the floor with counting days.