need help so that the results can be as I want

[roykana]

Dear All master,
if i use the code below after select range in column a then the results appear in column b and i want after column b the desired results appear in column c. Please solve

thanks
roykana

Sub GetFileNamebykana()

Dim title As String
Dim Rng As Range
Dim selection As Range
Dim splitpath As Variant

On Error Resume Next
title = "VBA by kana"


Set selection = Application.selection
Set selection = Application.InputBox("Range", title, selection.Address, Type:=8)
For Each Rng In selection
    splitpath = VBA.Split(Rng.Value, "\")
    Rng.Offset(0, 1).Value = splitpath(UBound(splitpath, 1))

Next
End Sub

 

[roykana]

Note that the use of "Selection" in the code is not slowing anything down, because the code itself is not selecting anything, it is just running off the range that is selected at the time the code is run.
We can suppress screen updating, which should help speed up the code.
We can also dynamically run it against all records in column A with data.

So those updates would look like this:

Sub GetFileName2()

    Dim lr As Long
    Dim rng As Range
    Dim arr1() As String
    Dim arr2() As String
    Dim arr3() As String

    Application.ScreenUpdating = False

'   Find last row in column A with data
    Sheets("Master").Select
    lr = Cells(Rows.Count, "A").End(xlUp).Row
  
'   Pre-format column C for text
    Columns("C:C").NumberFormat = "@"

'   Loop through every cell in column A starting in row 2
    For Each rng In Range("A2:A" & lr)
        arr1 = Split(rng.Value, "\")
        rng.Offset(0, 1).Value = arr1(UBound(arr1, 1))
        arr2 = Split(arr1(UBound(arr1, 1)), "(")
        arr3 = Split(arr2(0), "-")
        rng.Offset(0, 2).Value = Replace(arr3(0), ".jpg", "")
    Next rng

    Application.ScreenUpdating = True

End Sub

Let me know if that is fast enough. If not, I have another idea that does not involve loops.

Dear Mr. Joe
Thank you very much, it went perfectly and you are as my teacher
Thanks
roykana

 

[Joe4]

You are welcome.

 

[roykana]

Dear Mr. Joe,
I have a little problem with the code you provided and my problem is marked in yellow.
because of your code which does the split for the "-" sign and if the "-' sign is at the beginning is there any solution

the result should be like below:

 

[Joe4]

You keep adding in twists/situations which weren't presented in your original post. When providing examples, it is best to try to post examples which cover all the different scenarios representing the different data formats that you have. Otherwise, the solutions you receive may work on all the examples you provided, but not your all your actual data.

Try this variation:

Sub GetFileName2()

    Dim lr As Long
    Dim rng As Range
    Dim arr1() As String
    Dim arr2() As String
    Dim arr3() As String

    Application.ScreenUpdating = False

'   Find last row in column A with data
    Sheets("Master").Select
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Pre-format column C for text
    Columns("C:C").NumberFormat = "@"

'   Loop through every cell in column A starting in row 2
    For Each rng In Range("A2:A" & lr)
        arr1 = Split(rng.Value, "\")
        rng.Offset(0, 1).Value = arr1(UBound(arr1, 1))
        arr2 = Split(arr1(UBound(arr1, 1)), "(")
        arr3 = Split(arr2(0), "-")
'       If first member of array is blank, choose the second
        If arr3(0) = "" Then
            rng.Offset(0, 2).Value = Replace(arr3(1), ".jpg", "")
        Else
            rng.Offset(0, 2).Value = Replace(arr3(0), ".jpg", "")
        End If
    Next rng

    Application.ScreenUpdating = True

End Sub

 

[roykana]

You keep adding in twists/situations which weren't presented in your original post. When providing examples, it is best to try to post examples which cover all the different scenarios representing the different data formats that you have. Otherwise, the solutions you receive may work on all the examples you provided, but not your all your actual data.

Try this variation:

Sub GetFileName2()

    Dim lr As Long
    Dim rng As Range
    Dim arr1() As String
    Dim arr2() As String
    Dim arr3() As String

    Application.ScreenUpdating = False

'   Find last row in column A with data
    Sheets("Master").Select
    lr = Cells(Rows.Count, "A").End(xlUp).Row
 
'   Pre-format column C for text
    Columns("C:C").NumberFormat = "@"

'   Loop through every cell in column A starting in row 2
    For Each rng In Range("A2:A" & lr)
        arr1 = Split(rng.Value, "\")
        rng.Offset(0, 1).Value = arr1(UBound(arr1, 1))
        arr2 = Split(arr1(UBound(arr1, 1)), "(")
        arr3 = Split(arr2(0), "-")
'       If first member of array is blank, choose the second
        If arr3(0) = "" Then
            rng.Offset(0, 2).Value = Replace(arr3(1), ".jpg", "")
        Else
            rng.Offset(0, 2).Value = Replace(arr3(0), ".jpg", "")
        End If
    Next rng

    Application.ScreenUpdating = True

End Sub

Dear Mr. Joe,

thank you for your reply. Sorry if I asked you with different conditions. After I tried your code, there was a little problem.

the result should be like below:

thanks
roykana

 

[Joe4]

The following will work on all the examples you provided, but would not work on an example where the first character of the file name is "-", but there is then also another "-" later in the thread.

Sub GetFileName2()

    Dim lr As Long
    Dim rng As Range
    Dim arr1() As String
    Dim arr2() As String
    Dim arr3() As String

    Application.ScreenUpdating = False

'   Find last row in column A with data
    Sheets("Master").Select
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Pre-format column C for text
    Columns("C:C").NumberFormat = "@"

'   Loop through every cell in column A starting in row 2
    For Each rng In Range("A2:A" & lr)
        arr1 = Split(rng.Value, "\")
        rng.Offset(0, 1).Value = arr1(UBound(arr1, 1))
        arr2 = Split(arr1(UBound(arr1, 1)), "(")
        arr3 = Split(arr2(0), "-")
'       If first member of array is blank, choose the second
        If Left(arr2(0), 1) = "-" Then
            rng.Offset(0, 2).Value = Replace(arr2(0), ".jpg", "")
        Else
            rng.Offset(0, 2).Value = Replace(arr3(0), ".jpg", "")
        End If
    Next rng

    Application.ScreenUpdating = True

End Sub

 

[roykana]

Dear Mr.Joe,

thank you very much this is running perfectly . Sorry if I've bothered you. You are my best teacher.
Thanks
roykana

 

[Joe4]

You are welcome.

No bother, I am just trying to give you advice so that you can get the best solution possible.
So many times I see people ask over-simplified questions that don't really represent their actual data, and the result is they get answers that work for their examples, but not their actual data.
So it usually best to be as detailed as possible, and post examples that cover most of the possibilities that you may encounter.

 

[roykana]

You are welcome.

No bother, I am just trying to give you advice so that you can get the best solution possible.
So many times I see people ask over-simplified questions that don't really represent their actual data, and the result is they get answers that work for their examples, but not their actual data.
So it usually best to be as detailed as possible, and post examples that cover most of the possibilities that you may encounter.

Thank you for your advice