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?

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

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?

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