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
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