Passing Arrays Between Worksheet Functions

H

HJBEAN

Hi,

I am trying to write some code which has an array to take values from one
worksheet and pastes them in another. I am trying to pass my array through
with no success. I keep getting type mismatch and various other errors.

This is the code:
Sub CollectData()

Dim Date_str(12, 12) As String
Dim Data_str As String
Dim Find_str As String
Dim ThisRow As Integer

Data_str = "Date"
ThisRow = ActiveCell.Row
Find_str = ActiveSheet.Cells(ThisRow - 1, 1).Value

Do Until Find_str = Data_str
ThisRow = ThisRow - 1
Find_str = ActiveSheet.Cells(ThisRow - 1, 1).Value
Loop

i = 1

For e = ThisRow To ThisRow + 11
Date_str(1, i) = ActiveCell.Value
Next

Call Sheet9.Order(Date_str)

End Sub

And the other sheet:
Sub Order(ByVal Date_str As String)

For i = 1 To 12
Date_str(1, i) = ActiveSheet.Cells(i, 1).Value
Next

End Sub

Any Help?
 
D

Dave Peterson

Try:

Sub Order(ByRef Date_str() As String)

You're passing an array--not a simple string and when you pass arrays, you have
to use byRef.
 
R

Rick Rothstein

Try changing your Order procedure header to this...

Sub Order(ByVal Date_str() As String)

Note the empty parentheses telling the Order subroutine to expect a String
array.
 
R

Rick Rothstein

You can omit the ByRef keyword if you want as it is the default method of
passing arguments in VB.
 

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