Shockley:
I define as follow
Dim j As Long, sName As String, cell As Range, sTest As
String, i As Long, s As String
the macro run, but it overlay on the ColumnC, so can we
add one more code to insert blank Column on columnC first
then run this macro, otherwise I need to manually insert
blank column on each sheets, then run, it work. I use
Tester1() run, also can you explain each line of your
coding, I really appreciate your help.

Lillian



-----Original Message-----
Lillian,

It sounds like you have your vb Editor set to require
variable declaration.
Do you see "Option Explicit" at the top of the module?
Remove that
statement, and the macros should run OK, but let me know
if they don't.

You can turn off this option in the vb Editor with

Tools | Options | Editor tab | Unselect "Require Variable
Declaration

Or you can leave it on and declare every variable. If you
leave it on, every
time you insert a new module you will see the "Option
Explicit" statement at
the top. For beginners, it is recommended that it be
used, as it saves lots
of time debugging silly errors like spelling that are
easily overlooked.

Regards,
Shockley


"Lillian" wrote in
message
...
Shockley:
When I run this macro, it complain that j variable not
define, which I define as long, then complain sName not
define, can you help me more on this, thanks.


Lillian
-----Original Message-----
Lillian,
Here's two methods of doing what you want.
HTH,
Shockley


Sub Tester1()
For j = 1 To 3
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
For Each cell In .Columns(2).Cells
sTest = cell.Text
If sTest < Empty Then
For i = 1 To Len(sTest)
s = Right(sTest, i)
If Left(s, 1) = "\" Then
s = Right(s, Len(s) - 1)
Exit For
End If
Next i
.Cells(cell.Row, 3) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub

Sub Tester2()
For j = 1 To 3
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
For Each cell In .Columns(2).Cells
sTest = cell.Text
If sTest < Empty Then
OldSlashPos = 0
Do
SlashPos = InStr(1, sTest, "\")
If SlashPos 0 Then
OldSlashPos = OldSlashPos +
SlashPos
sTest = Mid(sTest, SlashPos
+ 1)
Else: Exit Do
End If
Loop
.Cells(cell.Row, 2) = sTest
Else: Exit For
End If
Next cell
End With
Next j
End Sub



"Lillian" wrote
in
message
...
I have one excel spreed sheet, they are sheet1,
sheet2,
sheet3 within this worksheet is called update.xls, on
each sheet1, sheet2, sheet3 in ColumnB each row has
real
long directory, example:
J:\files\docfiles\amaya01\demand.mcp.wpd, I need just
last file name, which is after last slash "\"
deemand.mcp.wpd, all entire ColumnB has 1000 rows has
different directory, all I need is the file name, do
not
care about the long directory, how to write the macro
just chop out last slash "\" before, and just need
the
file name.

So this macro need in the loop for
sheet1,sheet2,sheet3.
thanks for the help

Lillian


.



.