VBA with CHAR(1)

[VBA_uk_85]

Hi all.

I have a formula that works on a spreadsheet but char(1) isn't being recognised in VBA.

Worksheet C1 = RIGHT(C2,TRIM(FIND(CHAR(1),SUBSTITUTE(C2,"\",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2,"\","")))))+1)
Worksheet C2 = C:\Users\Name\Documents\Work\laugh GIFs.xlsm
The result in C1 = laugh GIFs.xlsm

The idea is to extract the file name by finding the Nth occurrence of "\" and then use that position to use the Right function.

So in VBA i've got..
(bare in mind C2 is now a variant called FileNm)

[TABLE="width: 554"]
<tbody>[TR]
[TD="class: xl63, width: 554"]Name =  Right(FileNm, Trim(Application.WorksheetFunction.Find(char(1),worksheetFunction.Substitute(char(1), WorksheetFunction.Substitute(FileNm,  "\", char(1), Len(FileNm) -  Len(WorksheetFunction.Substitute(FileNm, "\", ""))))) +  1))[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas??
Thanks in advance

 

[dave3009]

Use Chr instead or CHAR

 

[joeu2004]

Use Chr instead or CHAR

.... And VBA Trim does not behave the same as Excel TRIM. Check the help descriptions. You might want to use WorksheetFunction.Trim instead.

 

[dave3009]

If you just want to return the last part after the backslash \ then try this

Sub VBA_UK()
Dim str As Variant
str = Split(FileNm, "\")
Name = str(UBound(str))
End Sub

 

[Rick Rothstein]

If you just want to return the last part after the backslash \ then try this

Sub VBA_UK()
Dim str As Variant
str = Split(FileNm, "\")
Name = str(UBound(str))
End Sub

You can do that with a single line of code by using InStrRev...

Name = Mid(Filename, InStrRev(FileName, "\") + 1)