LucasLondon

07-01-2009, 11:04 AM

I have developed the code below which calculates some outputs based on looping through several test parameters (a series of numbers) and copies the outputs/calculation results for each different parameter into a summary sheet. However, instead of recording the results for all the parameters (lets assume there's 100) is it possible for VBA to store all the outputs in memory and once it's looped through all the parameters for the formula, only paste the results of the best parameter (as denoted as the maximum of one of the output cells - N2)? I read somewhere this was possible but don't know if it's true or how to do it.

The basic structure of the macro is:

1) A source sheet with the raw parameters - sheet called "Potential" from where each parameter is lifted

2) Calculation sheet where parameter is copied into to be used in a calculation of the output

3) Copy results for each parameter and paste into a summary sheet called results

On the current version, I get all results in the summary sheet for each parameter, but I only want to paste the result where N2 is maximised. Is there a way to do this without going into the result sheet and deleting all the rows that does not meet the criteria (i.e that does not have the maximum value in N2 ((the calculation sheet)), stored in column B in the summary sheet.

Thanks,

Lucas

Sub Parameters()

Application.ScreenUpdating = False

Dim x As Integer

Sheets("Potential").Select

For i = 2 To 100

Cells(i, 1).Copy

Sheets("Calculation").Select

Range("J2").PasteSpecial Paste:=xlPasteValues 'paste here for find function

'Run macro code

Range("M1:M5").Copy

Sheets("Results").Select

Range("A5000").Select

ActiveCell.End(xlUp).Select

ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).PasteSpecial Paste:=xlPasteValues, Transpose:=True

Sheets("Potential").Select

Next i

Application.ScreenUpdating = True

End Sub

The basic structure of the macro is:

1) A source sheet with the raw parameters - sheet called "Potential" from where each parameter is lifted

2) Calculation sheet where parameter is copied into to be used in a calculation of the output

3) Copy results for each parameter and paste into a summary sheet called results

On the current version, I get all results in the summary sheet for each parameter, but I only want to paste the result where N2 is maximised. Is there a way to do this without going into the result sheet and deleting all the rows that does not meet the criteria (i.e that does not have the maximum value in N2 ((the calculation sheet)), stored in column B in the summary sheet.

Thanks,

Lucas

Sub Parameters()

Application.ScreenUpdating = False

Dim x As Integer

Sheets("Potential").Select

For i = 2 To 100

Cells(i, 1).Copy

Sheets("Calculation").Select

Range("J2").PasteSpecial Paste:=xlPasteValues 'paste here for find function

'Run macro code

Range("M1:M5").Copy

Sheets("Results").Select

Range("A5000").Select

ActiveCell.End(xlUp).Select

ActiveCell.Offset(RowOffset:=1, ColumnOffset:=0).PasteSpecial Paste:=xlPasteValues, Transpose:=True

Sheets("Potential").Select

Next i

Application.ScreenUpdating = True

End Sub