need help so that the results can be as I want

roykana

Active Member
Joined
Mar 8, 2018
Messages
311
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
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
 

Attachments

  • desired result.JPG
    desired result.JPG
    54.3 KB · Views: 12
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:
VBA Code:
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
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
PROBLEM.JPG

the result should be like below:
RESULT.JPG
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
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:
VBA Code:
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.
PROBLEM-1.JPG

the result should be like below:
RESULT-1.JPG

thanks
roykana
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
Solution
Dear Mr.Joe,

thank you very much this is running perfectly . Sorry if I've bothered you. You are my best teacher.
Thanks
roykana
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top