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

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

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


.

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


.

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


.



.

Shockley:

Hi, I forgot to tell you that once the file name has
been replaced in Column C, then Column B need to be delete
as well. because we already got the result we need in
column C we do not need column B any more. so we need add
one more code for insert blank column into Column C and
and one more code for deleted Column B for the final, how
we add this two line in between your macro.

Thank you so much for the help, you are great.


Lillian
-----Original Message-----
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


.



.

.

Lillian,

Below is the complete annotated version. I've added a line to insert a
column so that the original full path ends up in column 3 and the file name
goes in column 2, which is how I think you want it. If you would rather just
overwrite the full path with the filename in column 2, I put a version that
will do that below the annotated version (it just involves changing one
column number and removing the insert command).

To round out your understanding of how the code works I recommend you look
up in the vb editor Help! some of the special commands and functions used in
the macro (use the animated help utility), which I list below:
For Each
Len
Right
Left
With

It also helps to use F8 to walk through the macro line by line, placing your
cursor over variables as you go to see how their values change.

Regards,
Shockley


Sub Tester1()
'Loop through the sheets:
For j = 1 To 3
'Assign current-sheet name to a variable:
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
.Columns(1).Insert
'Loop through the collection of cells _
in Column 3. 'cell' becomes a range _
variable:
For Each cell In .Columns(3).Cells
'Assign the full path to a string variable:
sTest = cell.Text
'Jump out of the For loop _
at the first empty cell and go to _
the next sheet ('next j'):
If sTest < Empty Then
'Create a string 's' of incrementing _
length, beginning with the _
right-most character of sTest and _
adding one character with each _
iteration of the loop, _
each time testing _
whether the added character is _
the slash character. When it is _
the slash character, remove it _
from the string, and the remaining _
string is your result (the string _
variable 's'):
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
'Put the result in Column 2:
.Cells(cell.Row, 2) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub


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, 2) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub

Lillian wrote

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.

Try (temporarily at least until you can get all variables declared)
unchecking 'Require Variable Declaration' in VBE's Tools|Options|Editor

--
David