PDA

View Full Version : Change Array Arguments to read horizontal instead of vertical



LucasLondon
08-11-2009, 05:53 AM
I have the macro below that was created for me by P45Cal. It loops through values in the sheet “potential” and puts it into J2 in the sheet “calculation”. It then copies the values in the cells from M1: M5 and pastes it into the sheet "results" transposed.


Sub Parameters()
Dim myOutput(2 To 20, 1 To 5) 'make array the size it needs to be.
For i = 2 To 20 'this 2 to 20 should match the 2 to 20 in the line above to do that you could use the next commented
out line:
'For i = LBound(myOutput) To UBound(myOutput)
Sheets("Calculation").Range("J2") = Sheets("Potential").Cells(i, 1).Value 'paste here for find function
'Run macro code
For j = 1 To 5
myOutput(i, j) = Sheets("Calculation").Range("M" & j).Value
Next j
Next i
Sheets("Results").Range("A2").Resize(UBound(myOutput) - LBound(myOutput) + 1, 5) = myOutput
End Sub



I’ve needed to change the layout of the sheet. So now instead of copying from M1 to M5 I want to copy from F4 to J4, i.e copy across columns instead of rows. I’ve tried to change the macro to reflect this below (indicated by the word changed) but it doesn’t seem to work . Hope someone can help.

Thanks,

Lucas



Parameters_latest_test()
Dim myOutput(2 To 20, 1 To 5)
For i = 2 To 20
'changed range reference
Sheets("Calculation").Range("R2") = Sheets("Potential").Cells(i, 1).Value 'Changed
'Run macro code
For j = 1 To 5
'changed references for row and columns
myOutput(j, i) = Sheets("Calculation").Range(j & 4).Value
Next j
Next i
Sheets("Results").Range("A2").Resize(UBound(myOutput) - LBound(myOutput) + 1, 5) = myOutput
End Sub

Bob Phillips
08-11-2009, 06:03 AM
Parameters_latest_test()
Dim myOutput(2 To 20, 1 To 5)
For i = 2 To 20
'changed range reference
Sheets("Calculation").Range("R2") = Sheets("Potential").Cells(i, 1).Value 'Changed
'Run macro code
For j = 1 To 5
'changed references for row and columns
myOutput(j, i) = Sheets("Calculation").Cells(4, j).Value
Next j
Next i
Sheets("Results").Range("A2").Resize(UBound(myOutput) - LBound(myOutput) + 1, 5) = myOutput
End Sub

LucasLondon
08-28-2009, 04:47 AM
Hello XLD,

Thanks for the update. When I try to run this it returns a run-time error 9: Subscript out of range. Debugging takes me to the line:

myOutput(j, i) = Sheets("Calculation").Cells(4, j).Value

Bob Phillips
08-28-2009, 05:26 AM
I just changed the code to what you needed, I didn't test it.

Testing it now, I see that the array myOutput is neither getting sized or loaded.

LucasLondon
08-28-2009, 07:51 AM
Hi XLD,

Not sure what that means but I'm sure there was fundamental problem as I don't see anywhere in the code that identifies which columns to (FtoJ) to extract the values from to populate the array.

I've tried changing For j = 1 To 5 to For j = 6 To 10 but that doesn't not work. (Assumed that the number could be interpreted as the column numbers.

Anything else I can try or would I have to add additional code?

Thanks,

Lucas

Bob Phillips
08-28-2009, 09:26 AM
I think I am losing it. Try this instead



Sub Parameters_latest_test()
Dim myOutput(2 To 20, 1 To 5)
Dim i As Long, j As Long
For i = 2 To 20
'changed range reference
Sheets("Calculation").Range("R2") = Sheets("Potential").Cells(i, 1).Value 'Changed
'Run macro code
For j = 1 To 5
'changed references for row and columns
myOutput(i, j) = Sheets("Calculation").Cells(4, j).Value
Next j
Next i
Sheets("Results").Range("A2").Resize(UBound(myOutput) - LBound(myOutput) + 1, 5) = myOutput
End Sub