OK... here's what I came up with.
I'm sure there are MANY other ways of doing this, but this uses some
concepts that it would be good for you to know.
You described two problems:
1) Sometimes, there is a single space after the first character of the movement
type: "D 0970"
2) Add a suffix to the sheet name when a duplication occurs.
So...
----------------------------------------------------------------------------------------------
1) Sometimes, there is a single space after the first character of the movement
type: "D 0970"
----------------------------------------------------------------------------------------------
When testing for the space, (If (Mid(tMvt, Pos, 1) <> " ") Then) instead of
exiting
the loop when you find one, check to see if the space is EARLY in the string.
If Pos > 3 Then Exit For
That way, if the string is "D0970", "D 0970", or even "D 0970", it will skip
the space, but
continue looking.
----------------------------------------------------------------------------------------------
2) Add a suffix to the sheet name when a duplication occurs.
----------------------------------------------------------------------------------------------
If your script attempts to rename a sheet to a name that already exists, it
will
cause an error
message.
You can turn off this error message with:
On Error Resume Next
then, you can check to see if an error occurred.
If all is well, Err.Number = 0, if an error occurs, Err.Number will NOT be 0.
In this case, Err.Number is 1004 and Err.Description is:
"Cannot rename a sheet to the same name as another sheet, a referenced object
library or a workbook referenced by Visual Basic."
The next thing you need to know is that there are 256 characters in the ASCII
character set (and Extended ASCII)
(numbered 0-255)
These can be retreived using the chr() function.
It happens that:
The Upper Case letters A-Z are Chr(65-90) M = Chr(77)
The Lower Case letters a-z are Chr(97-122) m = Chr(109)
So, if you wanted to append the letter "B" to your Movement and Docdate string
for the sheet name, use:
ActiveSheet.Name = Mvt & " " & DocDate & Chr(66)
But then, if THAT exists, you need to try "C", then "D".. and so on.
since the ASCII index is numeric, you can use a simple loop:
' Attempt Letters B-M
For inx = 66 To 77
Err.Clear
ActiveSheet.Name = Mvt & " " & DocDate & Chr(inx)
If Err.Number = 0 Then Exit For
Next inx
----------------------------------------------------------------------------------------------
With both of these solutions, your macro ends up looking like:
Sub Sheet_Cleanup()
Dim R, DocDate, Pos, tMvt, Mvt
Dim inx
For R = 1 To 100
If (Left(Cells(R, "A"), 5) = "Date:") Then
DocDate = Replace(Replace(Replace(Left(Cells(R, "A").Value, 15), "Date:",
""), " ", ""), "/", "")
If (R > 1) Then
Rows("1:" & R - 1).Delete Shift:=xlUp
Exit For
End If
End If
Next R
For R = 1 To 100
If (InStr(1, Cells(R, "A").Value, "Movement") > 0) Then
Pos = InStr(1, Cells(R, "A").Value, "Movement")
tMvt = Trim(Mid(Cells(R, "A").Value, Pos + 12, 20))
Mvt = ""
For Pos = 1 To Len(tMvt)
If (Mid(tMvt, Pos, 1) <> " ") Then
Mvt = Mvt & Mid(tMvt, Pos, 1)
Else
If Pos > 3 Then Exit For
End If
Next Pos
End If
Next R
' Upper Case A-Z are Chr(65-90) M = Chr(77)
' Lower Case a-z are Chr(97-122) m = Chr(109)
On Error Resume Next
Err.Clear
ActiveSheet.Name = Mvt & " " & DocDate
If (Err.Number > 0) Then
' Attempt Letters B-M
For inx = 66 To 77
Err.Clear
ActiveSheet.Name = Mvt & " " & DocDate & Chr(inx)
If Err.Number = 0 Then Exit For
Next inx
End If
End Sub
hope this gives you some new ideas to work with!!!
Paul
----- Original Message ----
> From: Paul Schreiner <schreiner_p...@att.net>
> To: excel-macros@googlegroups.com
> Sent: Fri, December 3, 2010 7:54:24 AM
> Subject: Re: $$Excel-Macros$$ Sheet rename based on data in sheet & delete row
>
> Sorry, got distracted.
> I'll take a look and post something soon.
>
> Paul
>
>
>
> ----- Original Message ----
> > From: JsinSk <jsin...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Wed, December 1, 2010 12:24:49 PM
> > Subject: Re: $$Excel-Macros$$ Sheet rename based on data in sheet & delete
>row
> >
> > Paul,
> >
> > I have a another other question (excluding the previous one listed
> > here in previous post).
> >
> > Sometimes the movement number has a single space between the letters
> > and numbers that make up the flight number. Is there a way to include
> > this information after the space (Total space will never exceed six
> > characters/spaces). At current it doesn't include anything after a
> > space.
> >
> > --
>>----------------------------------------------------------------------------------
>-
> >-
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
> >
> > To post to this group, send email to excel-macros@googlegroups.com
> >
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
> >
>
> --
>----------------------------------------------------------------------------------
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
