Passing Arrays Between Worksheet Functions
 

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?

Passing Arrays Between Worksheet Functions
 

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.



HJBEAN wrote:

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?

--

Dave Peterson

Passing Arrays Between Worksheet Functions
 

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.

--
Rick (MVP - Excel)


"HJBEAN" wrote in message
...
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?

Passing Arrays Between Worksheet Functions
 

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

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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.



HJBEAN wrote:

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?

--

Dave Peterson