modify existing macro

S

SteveDB1

Howdie all.

I have a macro that I've made based on a recorded macro.
It sets the headers, and footers of a worksheet.
I've been able to set it up so that it inputs the digits of the workbook's
name (I specifically did not want to use &[file]).

At this point, I was thinking that I'd like to use the file name as a basis
to set one more element.

I.e.,

MyClmVar=fname 'already set up
SSide = "P.11489, C. 4827" 'newcomer

here's what I have so far.

-----------------------------
Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Right(fname, InStr(fname, "-") - 1) 'removes everything except last 3
'digits of file name

'this next next part is what I am having trouble with.
'Based on the watch's that I've set, myFooter never contains any values.
'thus my question here becomes-- how can I set a specific list of values--
from 231
'through 266 inclusive to be the values for myFooter?
'I.e., myfooter(231) has the value 231, etc.... through to myfooter(266) has
the
'value 266.
'As I'm specifically interested in comparing the fname1 to the value of
myfooter(i)
'for my if test.


If fname1 Like ["myFooter()"] Then


With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With

'the balance of this works great.
Else

With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End If


End Sub

Your helps are appreciated.
 
S

SteveDB1

I want to focus on setting a list of values that will populate the myFooter()
string/array.
I'd give the rest so you could see where I wanted to go with it.

'this next next part is what I am having trouble with.
'Based on the watch's that I've set, myFooter never contains any values.
'thus my question here becomes-- how can I set a specific list of values-- from 231
'through 266 inclusive to be the values for myFooter?
'I.e., myfooter(231) has the value 231, etc.... through to myfooter(266) has
the value 266.
'As I'm specifically interested in comparing the fname1 to the value of
myfooter(i)
'for my if test.


If fname1 Like ["myFooter()"] Then


With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With
 
P

Per Jessen

Hi Steve

The value in fname1 is not what you expect.

You have never assigned any values to your array. You can use a "Select
Case" statement.

Look at this:

Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Mid(fname, InStr(fname, "-") + 1) 'removes everything except last 3
'digits of file name

Select Case fname1
Case 231 To 266
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With
'the balance of this works great.
Case Else
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End Select
End Sub


Regards,
Per
 
S

SteveDB1

Per,

Thank you.
That solved it.
I had considered a case statement but did not know it could be done in that
manner-- a range of numeric values "1 to XXX"
I thought it had to be individual numbers-- which would've given me a select
statement with 37 cases.

Definitely something I'll be able to use in the future too.
Again-- thank you.


Per Jessen said:
Hi Steve

The value in fname1 is not what you expect.

You have never assigned any values to your array. You can use a "Select
Case" statement.

Look at this:

Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Mid(fname, InStr(fname, "-") + 1) 'removes everything except last 3
'digits of file name

Select Case fname1
Case 231 To 266
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With
'the balance of this works great.
Case Else
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End Select
End Sub


Regards,
Per

SteveDB1 said:
Howdie all.

I have a macro that I've made based on a recorded macro.
It sets the headers, and footers of a worksheet.
I've been able to set it up so that it inputs the digits of the workbook's
name (I specifically did not want to use &[file]).

At this point, I was thinking that I'd like to use the file name as a
basis
to set one more element.

I.e.,

MyClmVar=fname 'already set up
SSide = "P.11489, C. 4827" 'newcomer

here's what I have so far.

-----------------------------
Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Right(fname, InStr(fname, "-") - 1) 'removes everything except
last 3
'digits of file name

'this next next part is what I am having trouble with.
'Based on the watch's that I've set, myFooter never contains any values.
'thus my question here becomes-- how can I set a specific list of values--
from 231
'through 266 inclusive to be the values for myFooter?
'I.e., myfooter(231) has the value 231, etc.... through to myfooter(266)
has
the
'value 266.
'As I'm specifically interested in comparing the fname1 to the value of
myfooter(i)
'for my if test.


If fname1 Like ["myFooter()"] Then


With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With

'the balance of this works great.
Else

With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End If


End Sub

Your helps are appreciated.
 
S

SteveDB1

Hi Per,
Again, thank for making that so doggone simple.

Now, I need to side step for a moment here, and ask the following.

I have 4 files whose names are non-standard, and have more than a single 3
digit set of numbers.
I.e., 178-262, 179-257, 179½-206-259, and the worst one- 207½-265½

My reason for putting fname1 in there was to further remove the primary
numbers, and the DTR- off, so I could get to the last set, in the case of
these 4, the 262, 257, 259 and 265½. While you are indeed correct that fname1
was not what one would expect, it accounted for all 4 of those files.

My hope was to do a "pattern comparison" to see if the active fname matched,
and if so, perform my first task. Thus, I had

if fname1 like ...... then
task A
else
task B


The way yours is written it allows for the entire file name without the
"DTR-" and file extension.
I.e., when I tested the 207½-265½ file, fname1 became 207½-265½. I'll assume
that it'd do likewise for the other 3 non-standard file names too.

I had used the way I had it because I need to get those 4 anomalies as well.

Using the select case as you have it, can I catch those 4 with it-- without
doing 4 more case functions?
case 233 to 266
Task A
case 178-262
Task A
case 179-257
Task A
case 207½-265½
Task A
case else
Task B

Again, thank you.


Per Jessen said:
Hi Steve

The value in fname1 is not what you expect.

You have never assigned any values to your array. You can use a "Select
Case" statement.

Look at this:

Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Mid(fname, InStr(fname, "-") + 1) 'removes everything except last 3
'digits of file name

Select Case fname1
Case 231 To 266
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With
'the balance of this works great.
Case Else
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End Select
End Sub


Regards,
Per

SteveDB1 said:
Howdie all.

I have a macro that I've made based on a recorded macro.
It sets the headers, and footers of a worksheet.
I've been able to set it up so that it inputs the digits of the workbook's
name (I specifically did not want to use &[file]).

At this point, I was thinking that I'd like to use the file name as a
basis
to set one more element.

I.e.,

MyClmVar=fname 'already set up
SSide = "P.11489, C. 4827" 'newcomer

here's what I have so far.

-----------------------------
Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Right(fname, InStr(fname, "-") - 1) 'removes everything except
last 3
'digits of file name

'this next next part is what I am having trouble with.
'Based on the watch's that I've set, myFooter never contains any values.
'thus my question here becomes-- how can I set a specific list of values--
from 231
'through 266 inclusive to be the values for myFooter?
'I.e., myfooter(231) has the value 231, etc.... through to myfooter(266)
has
the
'value 266.
'As I'm specifically interested in comparing the fname1 to the value of
myfooter(i)
'for my if test.


If fname1 Like ["myFooter()"] Then


With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With

'the balance of this works great.
Else

With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End If


End Sub

Your helps are appreciated.
 
P

Per Jessen

Hi Steve

Thanks for your reply.

Add this after fnam1=mid(.... to get the last three (four) digits:

If InStr(fname1, "-") > 0 Then
fname1 = Mid(fname1, InStr(fname1, "-") + 1)
End If

In the case statement you can also use:

Case 233 to 266, 178-262, 179-257

Best regards,
Per


SteveDB1 said:
Hi Per,
Again, thank for making that so doggone simple.

Now, I need to side step for a moment here, and ask the following.

I have 4 files whose names are non-standard, and have more than a single 3
digit set of numbers.
I.e., 178-262, 179-257, 179½-206-259, and the worst one- 207½-265½

My reason for putting fname1 in there was to further remove the primary
numbers, and the DTR- off, so I could get to the last set, in the case of
these 4, the 262, 257, 259 and 265½. While you are indeed correct that
fname1
was not what one would expect, it accounted for all 4 of those files.

My hope was to do a "pattern comparison" to see if the active fname
matched,
and if so, perform my first task. Thus, I had

if fname1 like ...... then
task A
else
task B


The way yours is written it allows for the entire file name without the
"DTR-" and file extension.
I.e., when I tested the 207½-265½ file, fname1 became 207½-265½. I'll
assume
that it'd do likewise for the other 3 non-standard file names too.

I had used the way I had it because I need to get those 4 anomalies as
well.

Using the select case as you have it, can I catch those 4 with it--
without
doing 4 more case functions?
case 233 to 266
Task A
case 178-262
Task A
case 179-257
Task A
case 207½-265½
Task A
case else
Task B

Again, thank you.


Per Jessen said:
Hi Steve

The value in fname1 is not what you expect.

You have never assigned any values to your array. You can use a "Select
Case" statement.

Look at this:

Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Mid(fname, InStr(fname, "-") + 1) 'removes everything except
last 3
'digits of file name

Select Case fname1
Case 231 To 266
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With
'the balance of this works great.
Case Else
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End Select
End Sub


Regards,
Per

SteveDB1 said:
Howdie all.

I have a macro that I've made based on a recorded macro.
It sets the headers, and footers of a worksheet.
I've been able to set it up so that it inputs the digits of the
workbook's
name (I specifically did not want to use &[file]).

At this point, I was thinking that I'd like to use the file name as a
basis
to set one more element.

I.e.,

MyClmVar=fname 'already set up
SSide = "P.11489, C. 4827" 'newcomer

here's what I have so far.

-----------------------------
Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Right(fname, InStr(fname, "-") - 1) 'removes everything except
last 3
'digits of file name

'this next next part is what I am having trouble with.
'Based on the watch's that I've set, myFooter never contains any
values.
'thus my question here becomes-- how can I set a specific list of
values--
from 231
'through 266 inclusive to be the values for myFooter?
'I.e., myfooter(231) has the value 231, etc.... through to
myfooter(266)
has
the
'value 266.
'As I'm specifically interested in comparing the fname1 to the value of
myfooter(i)
'for my if test.


If fname1 Like ["myFooter()"] Then


With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With

'the balance of this works great.
Else

With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End If


End Sub

Your helps are appreciated.
 
S

SteveDB1

Hi again Per,
I like the second IF statement. It covers all but one of the non-standard
file names though.
I then set a layer deeper with an elseif using the instr() statement, and
that did not cover the issue either.

I.e., the 179½-206-259 when stripped by your next if statement, becomes
206-259.
I tried a right(fname1, instr(fname1, "-")) and that only worked in specific
cases, but nullified the other, more important elements I needed.

It's starting to appear that I'd be better off doing the

Case 231 to 266, "178_262", "179-257", "179½-206-259", "207½-265½"
Task A
Case Else
Task B

This was meant to be simple.....

Again, thank you for your helps. I really appreciate them.
Have a great evening.

Per Jessen said:
Hi Steve

Thanks for your reply.

Add this after fnam1=mid(.... to get the last three (four) digits:

If InStr(fname1, "-") > 0 Then
fname1 = Mid(fname1, InStr(fname1, "-") + 1)
End If

In the case statement you can also use:

Case 233 to 266, 178-262, 179-257

Best regards,
Per


SteveDB1 said:
Hi Per,
Again, thank for making that so doggone simple.

Now, I need to side step for a moment here, and ask the following.

I have 4 files whose names are non-standard, and have more than a single 3
digit set of numbers.
I.e., 178-262, 179-257, 179½-206-259, and the worst one- 207½-265½

My reason for putting fname1 in there was to further remove the primary
numbers, and the DTR- off, so I could get to the last set, in the case of
these 4, the 262, 257, 259 and 265½. While you are indeed correct that
fname1
was not what one would expect, it accounted for all 4 of those files.

My hope was to do a "pattern comparison" to see if the active fname
matched,
and if so, perform my first task. Thus, I had

if fname1 like ...... then
task A
else
task B


The way yours is written it allows for the entire file name without the
"DTR-" and file extension.
I.e., when I tested the 207½-265½ file, fname1 became 207½-265½. I'll
assume
that it'd do likewise for the other 3 non-standard file names too.

I had used the way I had it because I need to get those 4 anomalies as
well.

Using the select case as you have it, can I catch those 4 with it--
without
doing 4 more case functions?
case 233 to 266
Task A
case 178-262
Task A
case 179-257
Task A
case 207½-265½
Task A
case else
Task B

Again, thank you.


Per Jessen said:
Hi Steve

The value in fname1 is not what you expect.

You have never assigned any values to your array. You can use a "Select
Case" statement.

Look at this:

Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Mid(fname, InStr(fname, "-") + 1) 'removes everything except
last 3
'digits of file name

Select Case fname1
Case 231 To 266
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With
'the balance of this works great.
Case Else
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End Select
End Sub


Regards,
Per

"SteveDB1" <[email protected]> skrev i meddelelsen
Howdie all.

I have a macro that I've made based on a recorded macro.
It sets the headers, and footers of a worksheet.
I've been able to set it up so that it inputs the digits of the
workbook's
name (I specifically did not want to use &[file]).

At this point, I was thinking that I'd like to use the file name as a
basis
to set one more element.

I.e.,

MyClmVar=fname 'already set up
SSide = "P.11489, C. 4827" 'newcomer

here's what I have so far.

-----------------------------
Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Right(fname, InStr(fname, "-") - 1) 'removes everything except
last 3
'digits of file name

'this next next part is what I am having trouble with.
'Based on the watch's that I've set, myFooter never contains any
values.
'thus my question here becomes-- how can I set a specific list of
values--
from 231
'through 266 inclusive to be the values for myFooter?
'I.e., myfooter(231) has the value 231, etc.... through to
myfooter(266)
has
the
'value 266.
'As I'm specifically interested in comparing the fname1 to the value of
myfooter(i)
'for my if test.


If fname1 Like ["myFooter()"] Then


With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With

'the balance of this works great.
Else

With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End If


End Sub

Your helps are appreciated.
 
P

Per Jessen

Hi Steve

Thanks for your reply. I'm glad to help.

Have you tried to repeat the if statement ?

Regards,
Per

SteveDB1 said:
Hi again Per,
I like the second IF statement. It covers all but one of the non-standard
file names though.
I then set a layer deeper with an elseif using the instr() statement, and
that did not cover the issue either.

I.e., the 179½-206-259 when stripped by your next if statement, becomes
206-259.
I tried a right(fname1, instr(fname1, "-")) and that only worked in
specific
cases, but nullified the other, more important elements I needed.

It's starting to appear that I'd be better off doing the

Case 231 to 266, "178_262", "179-257", "179½-206-259", "207½-265½"
Task A
Case Else
Task B

This was meant to be simple.....

Again, thank you for your helps. I really appreciate them.
Have a great evening.

Per Jessen said:
Hi Steve

Thanks for your reply.

Add this after fnam1=mid(.... to get the last three (four) digits:

If InStr(fname1, "-") > 0 Then
fname1 = Mid(fname1, InStr(fname1, "-") + 1)
End If

In the case statement you can also use:

Case 233 to 266, 178-262, 179-257

Best regards,
Per


SteveDB1 said:
Hi Per,
Again, thank for making that so doggone simple.

Now, I need to side step for a moment here, and ask the following.

I have 4 files whose names are non-standard, and have more than a
single 3
digit set of numbers.
I.e., 178-262, 179-257, 179½-206-259, and the worst one- 207½-265½

My reason for putting fname1 in there was to further remove the primary
numbers, and the DTR- off, so I could get to the last set, in the case
of
these 4, the 262, 257, 259 and 265½. While you are indeed correct that
fname1
was not what one would expect, it accounted for all 4 of those files.

My hope was to do a "pattern comparison" to see if the active fname
matched,
and if so, perform my first task. Thus, I had

if fname1 like ...... then
task A
else
task B


The way yours is written it allows for the entire file name without the
"DTR-" and file extension.
I.e., when I tested the 207½-265½ file, fname1 became 207½-265½. I'll
assume
that it'd do likewise for the other 3 non-standard file names too.

I had used the way I had it because I need to get those 4 anomalies as
well.

Using the select case as you have it, can I catch those 4 with it--
without
doing 4 more case functions?
case 233 to 266
Task A
case 178-262
Task A
case 179-257
Task A
case 207½-265½
Task A
case else
Task B

Again, thank you.


:

Hi Steve

The value in fname1 is not what you expect.

You have never assigned any values to your array. You can use a
"Select
Case" statement.

Look at this:

Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Mid(fname, InStr(fname, "-") + 1) 'removes everything except
last 3
'digits of file name

Select Case fname1
Case 231 To 266
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar &
SSide
End With
'the balance of this works great.
Case Else
With ActiveSheet.PageSetup
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End Select
End Sub


Regards,
Per

"SteveDB1" <[email protected]> skrev i meddelelsen
Howdie all.

I have a macro that I've made based on a recorded macro.
It sets the headers, and footers of a worksheet.
I've been able to set it up so that it inputs the digits of the
workbook's
name (I specifically did not want to use &[file]).

At this point, I was thinking that I'd like to use the file name as
a
basis
to set one more element.

I.e.,

MyClmVar=fname 'already set up
SSide = "P.11489, C. 4827" 'newcomer

here's what I have so far.

-----------------------------
Sub FooterArray()
Dim SSide As String
Dim myFooter(231 To 266) As String
Dim fname As String
Dim fname1 As String
Dim MyClmVar As String

SSide = " P.11489, C.4827"

fname = ActiveWorkbook.Name 'this looks at the existing file's name

'remove extension
fname = Left(fname, InStr(fname, ".") - 1)


MyClmVar = fname

fname1 = Right(fname, InStr(fname, "-") - 1) 'removes everything
except
last 3
'digits of file name

'this next next part is what I am having trouble with.
'Based on the watch's that I've set, myFooter never contains any
values.
'thus my question here becomes-- how can I set a specific list of
values--
from 231
'through 266 inclusive to be the values for myFooter?
'I.e., myfooter(231) has the value 231, etc.... through to
myfooter(266)
has
the
'value 266.
'As I'm specifically interested in comparing the fname1 to the value
of
myfooter(i)
'for my if test.


If fname1 Like ["myFooter()"] Then


With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar & SSide
End With

'the balance of this works great.
Else

With ActiveSheet.PageSetUp
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
End With
End If


End Sub

Your helps are appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top