PDA

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

snb
02-24-2013, 01:49 PM
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.

snb
02-24-2013, 03:05 PM
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

GTO
02-25-2013, 08:03 PM
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