follow up to Gary's Student macro

S

Steve

hi all.
On the 10th of this month, Gary's Student provided me with a macro that
helped me do an array so I could do a comparison.
This morning I tried my final version of the macro and it threw a mismatch
error.
I looked at it, and I cannot identify the error's source.
As such, would you pease help me track this thing down so I can get it
working? It worked fine immediately following my final tweak as I put it into
place.

The code for the initial components are below.
Gary's code.
----------------------------------------------------------------
-------------------------------------------------------------

Sub StringAlong()
sArray = Split("alpha,beta,gamma,delta,zeta,eta,theta", ",")
sample = "mm"
For i = LBound(sArray) To UBound(sArray)
If InStr(sArray(i), sample) > 0 Then
MsgBox ("found it")
Exit Sub
End If
Next
MsgBox ("did not find it")
End Sub
---------------------------------
-----------------------------------

my variation below.

------------------------------------
-------------------------------


Dim fname As String
Dim fname1 As String
Dim myClmVar1 As String

Dim SSide 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)

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

'
Dim MyDate As Date

MyDate = Date
Dim MyClmVar As String
MyClmVar = fname

myClmVar1 = MyClmVar & SSide
sArray =
Split("231,232,233,234,235,236,237,237A,238,239,240,241,242,243,243½,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265½,266", ",")
'the sArray is where the error is thrown, as a mismatch.
Application.ScreenUpdating = False

With ActiveSheet.PageSetUp
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetUp.PrintArea = ""
With ActiveSheet.PageSetUp
.RightHeader = "Printed on: &D"
.LeftFooter = "&""Arial,Bold""&14 " & MyDate
'-------------------------------------------------
If UBound(Filter(sArray, fname1)) >= 0 Then
.CenterFooter = "&""Arial,Bold""&14 " & myClmVar1
'MsgBox "your output is: " & myClmVar1
Else
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
'MsgBox "your output is: " & MyClmVar
End If
'------------------------------------------------
.........................
end with

Thank you.
 
M

meh2030

hi all.
On the 10th of this month, Gary's Student provided me with a macro that
helped me do an array so I could do a comparison.
This morning I tried my final version of the macro and it threw a mismatch
error.
I looked at it, and I cannot identify the error's source.
As such, would you pease help me track this thing down so I can get it
working? It worked fine immediately following my final tweak as I put it into
place.

The code for the initial components are below.
Gary's code.
----------------------------------------------------------------
-------------------------------------------------------------

Sub StringAlong()
sArray = Split("alpha,beta,gamma,delta,zeta,eta,theta", ",")
sample = "mm"
For i = LBound(sArray) To UBound(sArray)
If InStr(sArray(i), sample) > 0 Then
MsgBox ("found it")
Exit Sub
End If
Next
MsgBox ("did not find it")
End Sub
---------------------------------
-----------------------------------

my variation below.

------------------------------------
-------------------------------

Dim fname As String
Dim fname1 As String
Dim myClmVar1 As String

Dim SSide 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)

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

'
Dim MyDate As Date

MyDate = Date
Dim MyClmVar As String
    MyClmVar = fname

myClmVar1 = MyClmVar & SSide
 sArray =
Split("231,232,233,234,235,236,237,237A,238,239,240,241,242,243,243½,244,24­5,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,2­64,265½,266", ",")
'the sArray is where the error is thrown, as a mismatch.
 Application.ScreenUpdating = False

    With ActiveSheet.PageSetUp
        .PrintTitleRows = "$1:$7"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetUp.PrintArea = ""
    With ActiveSheet.PageSetUp
        .RightHeader = "Printed on: &D"
        .LeftFooter = "&""Arial,Bold""&14 " & MyDate
        '-------------------------------------------------
        If UBound(Filter(sArray, fname1)) >= 0 Then
            .CenterFooter = "&""Arial,Bold""&14 " & myClmVar1
                'MsgBox "your output is: " & myClmVar1
            Else
            .CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
                'MsgBox "your output is: " & MyClmVar
        End If
        '------------------------------------------------
........................
end with

Thank you.
Click to expand...

Steve,

Do you have "Require Variable Declaration" checked in Tools | Options
(Editor page)? Or in other words, does your code module have "Option
Explicit" in it? If it does, then you are likely getting a type
mismatch for your sArray variable. I don't see a Dim statement for
this variable in your code, but I'm guessing you declared sArray As
String. Change your data type to Variant (i.e. Dim sArray As Variant)
and see if it will work.

Best,

Matt Herbert
 
S

Steve

As an addendum,
My primary samples work fine, and have no troubles. And initially, my final
macro worked well too. I'd tested it three or four times after I put it in
place and had no errors.
This is why I don't understrand what's wrong.
Thank you.
Code below.
------------------------------------------------------
Dim SSide 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



sArray =
Split("231,232,233,234,235,236,237,237A,238,239,240,241,242,243,243½,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265½,266", ",")


If UBound(Filter(sArray, fname1)) >= 0 Then

MsgBox "you're working with claim: " & MyClmVar & SSide
Else
MsgBox "you're working with claim: " & MyClmVar
End If
 
S

Steve

Hi MAtt,
Thank you for the quick response.
I looked-- no, my require variable declaration is not checked. And no, I do
not have option explicit anywhere in the macro module.
Then, I did try setting my sArray to a declared type.
Neither dim sArray as variant, nor as string worked.
Any other ideas?
Again, thanks.
 
M

meh2030

Hi MAtt,
Thank you for the quick response.
I looked-- no, my require variable declaration is not checked. And no, I do
not have option explicit anywhere in the macro module.
Then, I did try setting my sArray to a declared type.
Neither dim sArray as variant, nor as string worked.
Any other ideas?
Again, thanks.





- Show quoted text -
Click to expand...

Steve,

Require Varialbe Declaration forces you to dimension all of your
variables. Checking this option will automatically write "Option
Explicit" at the top of any new Module you create. As you code,
Option Explicit forces the editor to inform you if you used a variable
that does not match the variables you dimensioned. This option is a
nice way of performing a "double check" against what you are doing in
real time, before you run your code (as well as when you run your
code).

I'm not quite sure why you are getting an error. Is it still a Type
Mismatch error? I copied your code, created a workbook called
"test-256.xls", and ran the code below. I didn't run into any
errors. You can copy and paste my code below and see if you are error
free; otherwise, your error may be coming from somewhere else in your
procedure. (I'm assuming you you didn't paste the entire procedure in
this post).

Sub TestCode()

Dim strSide As String
Dim strFileName As String
Dim strFileName1 As String
Dim strMyClaim As String
Dim varMyArray As Variant

strSide = ", P.11489, C.4827"
strFileName = ActiveWorkbook.Name
strFileName = Left(strFileName, InStrRev(strFileName, ".") - 1)
strMyClaim = strFileName
strFileName1 = Mid(strFileName, InStr(strFileName, "-") + 1)

varMyArray = Split("231,232,233,234,235,236,237,237A,238,239," & _
"240,241,242,243,243½,244,24­5,246,247,248,249," & _
"250,251,252,253,254,255,256,257,258,259,260," & _
"261,262,263,2­64265½,266", ",")

If UBound(Filter(varMyArray, strFileName1)) >= 0 Then
MsgBox "you're working with claim: " & strMyClaim & strSide
Else
MsgBox "you're working with claim: " & strMyClaim
End If

End Sub

Let me know if this helps you pin down your problem.

Matt
 
S

Steve

Hi again Matt,
Ok, I tried yours and it works fine.
If you look at the "Addendum" post I did below, you'll see my tester piece
that works well too. Which is why I cannot understand why there's a problem.
I'd used the same variable names, same functions, same structures, etc....
and the only difference is the operations that occur.
And even before I placed the whole code together, I'd tested the operations
because I wanted no troubles later. With each failure I went back, tried
something else, etc.... until I got a working model. Then, because I had
three version of this, I tested all 3 final variations, on worksheets that'd
be affected by each of the three, multiple times-- in its final state that'd
be released for use-- and they worked, really well-- exactly as expected.
Today was the first time I tried using it in the course of my daily
operations since I'd finished it because of how busy I'd been since
completing them.

I see the only real change was that you used different variable names, and
InStrRev in your left function, and then placed hyphens in two of the array
variable elements.
With the mismatch error, I do not get the large window, allowing me to
debug, end, etc...and that states the error number-- which I think is 13.
I only get a small, 1 x 1-1/2 window that just says compile error, type
mismatch, and has only an ok, and a help button on it.

Then, you are correct, I did not place the entire macro in my initial post.
Here is that macro, in its entirety.
------------------------------------------------------------------------------------
Sub AbstPgStUp_Footer(Control As IRibbonControl)

' AbstPgStUp_Footer Macro
Dim fname As String
Dim fname1 As String
Dim myClmVar1 As String

Dim SSide 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)
' this appears to remove the existing file's extension
fname1 = Mid(fname, InStr(fname, "-") + 1)
'removes everything except last 3 digits of file name

'
Dim MyDate As Date

MyDate = Date
Dim MyClmVar As String
MyClmVar = fname

myClmVar1 = MyClmVar & SSide
sArray =
Split("231,232,233,234,235,236,237,237A,238,239,240,241,242,243,243½,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265½,266", ",")




Application.ScreenUpdating = False

With ActiveSheet.PageSetUp
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetUp.PrintArea = ""
With ActiveSheet.PageSetUp
.RightHeader = "Printed on: &D"
.LeftFooter = "&""Arial,Bold""&14 " & MyDate
'-------------------------------------------------
If UBound(Filter(sArray, fname1)) >= 0 Then
.CenterFooter = "&""Arial,Bold""&14 " & myClmVar1
'MsgBox "your output is: " & myClmVar1
Else
.CenterFooter = "&""Arial,Bold""&14 " & MyClmVar
'MsgBox "your output is: " & MyClmVar
End If
'------------------------------------------------

.RightFooter = "&""Arial,Bold""&14Page &P of &N" & Chr(10) & "Tab: &A"
.LeftMargin = Application.InchesToPoints(1.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.FitToPagesWide = 1
.FitToPagesTall = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True

End With
End Sub

------------------------------------------------------------------------------------
The only parts I'd initially left out were the page set up portions, near
the bottom, as you can now see, that are pretty standard.
Thanks again for your help..... it really is appreciated.
BEst.
 
G

Gary''s Student

In my posted code the array was an array of Text. You sArray is a mixture of
numbers and Text.
InStr() should have string arguments.

Perhaps VBA is have problems handling the mixed array.
 
P

Per Jessen

Hi Steve

I tested the code below and I didn't recived any errors.

When the code errors out, which line is highlighted on clicking debug?

BTW: You turn off screenupdating, but you never turns it on again. As good
programming practice you should always declare all variables in the top of
the sub.

Hopes this helsp.
 
S

Steve

Hi Per,
The the line that's highlighted with the error is the:

sArray = split("231,232,......,266", ",")

line.
I've been over that string a dozen times and cannot identify what the
problem could be. I've tested the same exact string in other pre-run
variations and it works with no errors or any kind. That alone makes me think
that the split function is not the issue, but something else that's dependent
on the function.
I've even put some sample code elements in it-- as Rick, and GAry's Student
had with their original samples, and it worked fine, but my configuration has
an issue that escapes my eyes.
I also had a colleague with whom I collaborate regularly look it over, and
we went line by line, comparing variations, making sure nothing was out of
synch, and he couldn't identify the issue either.


And it's ONLY the string of numbers within the split function.
The double quotes surrounding the string of numbers are NOT highlighted, nor
is the comma, or second set of double quotes defining the delimiter.
It's only the numbers within the split function.


And thank you for pointing out the screen updating, and variable
dclarations. I'll take care of that, as well as the locations of my variable
declarations.


All that said, having multiple eyes look things over is deeply appreciated,
as it worked last week, and now it sticks on that one line.
Again, thank you for all of your help.
 
S

Steve

Hi again Per,

for the sake of the discussion, I made a new string of the numbers in the
split function, and placed that string in there-- replacing the oiriginal
string.
It throws the same error-- mismatch.

I also corrected the placement of my variable declarations, and put in a
screenupdating = true at the end of my code.
No other errors appear.....
 
M

meh2030

Hi Per,
The the line that's highlighted with the error is the:

sArray = split("231,232,......,266", ",")

line.
I've been over that string a dozen times and cannot identify what the
problem could be. I've tested the same exact string in other pre-run
variations and it works with no errors or any kind. That alone makes me think
that the split function is not the issue, but something else that's dependent
on the function.
I've even put some sample code elements in it-- as Rick, and GAry's Student
had with their original samples, and it worked fine, but my configurationhas
an issue that escapes my eyes.
I also had a colleague with whom I collaborate regularly look it over, and
we went line by line, comparing variations, making sure nothing was out of
synch, and he couldn't identify the issue either.

And it's ONLY the string of numbers within the split function.
The double quotes surrounding the string of numbers are NOT highlighted, nor
is the comma, or second set of double quotes defining the delimiter.
It's only the numbers within the split function.

And thank you for pointing out the screen updating, and variable
dclarations. I'll take care of that, as well as the locations of my variable
declarations.

All that said, having multiple eyes look things over is deeply appreciated,
as it worked last week, and now it sticks on that one line.
Again, thank you for all of your help.









...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

Steve,

This is indeed perplexing. Here are my thoughts this time:

(1) As for Application.ScreenUpdating = False, you don't actually have
to set it back to True. The program will automatically set it back to
True when it hits the End Sub line. Also, when you step though your
program via F8, you should still be able to watch the screen (i.e.
ScreenUpdating won't act as if it were set to False as you step
through your program).

(2) I don't know that the Split function is causing a potential
problem because Split "returns a zero-based, one-dimensional array
containing a specified number of substrings," but maybe try setting
your array manually with string data using the Array function (see
below). Maybe the Array function will yield better results.

Best,

Matt

Sub TestCode()

Dim strSide As String
Dim strFileNameOne As String
Dim strFileNameTwo As String
Dim strMyClaim As String
Dim varMyArray As Variant
Dim lngA As Long

strSide = ", P.11489, C.4827"
strFileNameOne = ActiveWorkbook.Name
strFileNameOne = Left(strFileNameOne, InStrRev(strFileNameOne, ".") -
1)
strMyClaim = strFileNameOne
strFileNameTwo = Mid(strFileNameOne, InStr(strFileNameOne, "-") + 1)

varMyArray = Array("231", "232", "233", "234", "235", "236", _
"237", "237A", "238", "239", "240", "241", _
"242", "243", "243½", "244", "24­5", "246", _
"247", "248", "249", "250", "251", "252", _
"253", "254", "255", "256", "257", "258", _
"259", "260", "261", "262", "263", "2­64265½", _
"266")

If UBound(Filter(varMyArray, strFileNameTwo)) >= 0 Then
MsgBox "you're working with claim: " & strMyClaim & strSide
Else
MsgBox "you're working with claim: " & strMyClaim
End If

End Sub
 
S

Steve

as a further check, I declared sArray as a variant, and then as a string.
Neither works.
I.e., it still throws the error.
Gary's Student made the comment last night that Instr() could be the issue
in that I'm using a numeric string for my split function--

"In my posted code the array was an array of Text. You sArray is a mixture
of numbers and Text.
InStr() should have string arguments.

Perhaps VBA is have problems handling the mixed array. "

However, if that's true, then would it not have shown up in my testing
phase, instead of now?
Also, would not you, and Matt experienced the same issue when you ran my
code?

Then, also for the sake of the discussion, I tried to use the numeric string
as a reference and received a byRef error.
I.e., I declared a new variable- ClaimNm as a string, then as a variant, and
set it equal to the string of numbers.
ClaimNm = "232,233,.....,266"
It threw a byRef argument type mismatch error with both string, and variant.
So, I set it back to the initial configuration.
 
S

Steve

Hi Matt,
Well, since my last response failed due to all the technical difficulties
I've been having with the newsgroups, I'll post again......
I have some good news.
I've taken a variation of your testcode macro you did, with the Array, and
used that, configuraing it exactly as you did, along with delcaring sArray as
variant and it works.
I then tested all three macros I'm using with this general structure, and
they all work.
So, for now, I'm going to consider this one resolved-- until/if I find some
other previously unknown bug, and desired modification.

Thank you for your help.
Gary's Student, thank you for your input.
Per, thank you as well.
All of your helps were/are deeply appreciated.
Have a great weekend.
Ciao.
Best.
 

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

Similar Threads

modify existing macro 7
Update Directory Path in Code 4
Open_Files_In_A_Directory 2
Excel VBA question...Need help.. 3
type mismatch error in macro in excel 0
Macro to send email with multiple attachment 0
Split & Replace Functions for MacWord & Word97? 0
Systematically Importing Data to Excel 0

Top