View Full Version : Solved: Byref argument type mismatch array
fredlo2010
02-24-2013, 09:44 AM
Hello guys I am creating a routine that will filter data several times and then create a file for each new file.
The code works the way intended except when I tried to put all my filtering criteria in an array. It gives me an error "Byref argument type mismatch" Note that I dont want several criteria I just want to loop though the array and filter one by one.
Thanks a lot for the help
Sub MainCode()
Dim varArray As Variant
Dim n As Long
varArray = Array("North", "South", "East", "West")
For n = 0 To 3
Call FilterData(varArray(n))
Call CopyData
'REMOVE THE FILTER TO REDO IT AGAIN FOR THE NEXT AREA
ActiveSheet.ShowAllData
ActiveSheet.Range("A1").AutoFilter
Next
'REMOVE THE FILTER TO LEAVE THE ORIGINAL FILE UNTOUCHED!
ActiveSheet.ShowAllData
ActiveSheet.Range("A1").AutoFilter
End Sub
Sub FilterData(var As String)
Dim lrow As Long
Dim lcol As Long
With Sheets("Sheet1")
'DETERMINE THE LAST ROW AND COLUMN
lrow = .Cells(Rows.Count, 1).End(xlUp).Row
lcol = .Cells(1, Columns.Count).End(xlToLeft).Column
'USE THE FILTER WITH THE VARIABLE THAT WILL BE PASSED BY THE FUNCTION
.Range("A1").AutoFilter
.Range(Cells(1, 1), Cells(lrow, lcol)).AutoFilter Field:=1, Criteria1:=var
End With
End Sub
fredlo2010
02-24-2013, 01:26 PM
Hello guys,
I already found a solution to my problem I added a new variable set to string and changed that in the loop
The final code will go like this
varArray = Array("North", "South", "East", "West")
For n = 0 To 3
textString = varArray(n)
'ADD THE VALUE TO SHEET2 TO USE LATER WITH THE NAME
ActiveWorkbook.Sheets("Sheet2").Range("A1").Value = textString
Call FilterData(textString)
Call CopyData
'REMOVE THE FILTER TO REDO IT AGAIN FOR THE NEXT AREA
ActiveSheet.ShowAllData
ActiveSheet.Range("A1").AutoFilter
Next
This suffices :
Sub M_snb()
with activesheet.cells(1).currentregion
for each it in array("North", "South", "East", "West")
.autofilter 1, it
.offset(1).copy activesheet.cells(1,20)
.autofilter
next
end with
End Sub
fredlo2010
02-24-2013, 02:06 PM
snb,
I dont understand your code.
Do you mean you didn't test it ?
fredlo2010
02-24-2013, 03:15 PM
If I am asking is because I tested it and it does not do what I want. I don't even think it does what's intended to do. Really I don't think a few lines of code like yours do what my code which contains even more codes clearly visible because I always Use the "call" command.
I don't want a new code I just wanted to know what was wrong with mine.
I am sorry but what you provided was totally useless it does not answer my question of "why Byref argument type mismatch array" at all.
Anyways its always the same with your posts and me just trying to shine instead of help.
Don't worry I already solved my problem and it was done by adding a simple new line and new variable to my code.
Its sad that a very good forum like this where we all help each other and the level of professionalism is outstanding has a contributor like you.
Thanks
Hello guys I am creating a routine that will filter data several times and then create a file for each new file.
The code works the way intended except when I tried to put all my filtering criteria in an array. It gives me an error "Byref argument type mismatch" Note that I dont want several criteria I just want to loop though the array and filter one by one.
Greetings Fred,
In looking at your code, the problem is not in passing the array per se, but that you are trying to pass ByRef (By Reference) one data type (the array is declared as a Variant), but the receiving procedure is expecting a String; hence the big "Kaboom!" from Excel.
As 'var' does not need adjusted (ie, we are not changing the values of the passed array's elements), I think you could have passed ByVal (By Value) like:
Sub FilterData(ByVal var As String)
See if this example code helps a bit. Step through it with the Locals window showing.
Sub StringPassedByRef(ByRef sometext As String)
sometext = sometext & " added text"
End Sub
Sub StringPassedByVal(ByVal sometext As String)
sometext = sometext & " added text"
MsgBox """sometext"" currently has the value: " & sometext & vbCrLf & _
"...but note that as it was passed ByVal, just a copy of the variable was passed," & vbCrLf & _
"and this variable dies in procedure, so MyText is never updated"
End Sub
Sub CallAsString()
Dim MyText As String
MyText = "Initial text"
StringPassedByRef MyText
'Note that the value of MyText is "Initial text added text" after StringPassedByRef()
'has run. This is because we passed ByRef, so MyText "went along for the ride" so-to-speak,
'and had its value changed while in StringPassedByRef().
MsgBox MyText
MyText = "Initial text"
StringPassedByVal MyText
MsgBox MyText
End Sub
Sub CallAsVariant_JustACopyOfTheValueIsPassedSoOkeyDokey()
Dim MyVariant
'As we are passing ByVal, the COPY of the Variant/Date can be coerced to a string
'in the receiving procedure, since dates, numbers, and of course text, can all be read as
' as a string
MyVariant = #2/25/2013#
StringPassedByVal MyVariant
MsgBox MyVariant
End Sub
Sub CallAsVariant_Kaboom_OhhhNooooo()
Dim MyVariant
'This will not compile.
MyVariant = #2/25/2013#
StringPassedByRef MyVariant
MsgBox MyVariant
End Sub
Does that help?
Mark
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.