VBA: Running Solver on Multiple Data Sets, Copying Output to Indexed Columns

[SconnorA]

An outline of my code is as follows;
1. Copy single column of data from raw data sheets, of which there are 5 in a separate workbook, to the curve fitting sheet (different workbook).
2. Run solver on these data to minimize the sum of the squares.
3. Copy the output to the i'th row of column A/B/C/D/E (A for first raw data fro sheet1, B for second etc.)
4. Repeat on the next column of raw data from the first sheet (do this until all data processed through Solver)
5. Move to the next sheet and repeat for all raw data.

I am having a few issues with the copying of raw data to the curve fitting sheet as well as the output to the indexed columns. As far as I know the Solver is running, yet on the first run through the macro there was no data showing in the required column and the output copying failed. Any help is greatly appreciated, here is the code for reference:


Option Explicit


Sub CurveFit()
Dim i As Single
Dim Output() As Single 'Define this dynamic array for the solver output


'Process the DMSO data
For i = 1 To 45 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "A").PasteSpecial xlPasteValues <---GET AN ERROR HERE
Next i


i = 0


' Process the NaCl Data
For i = 1 To 102 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate

Sheet6.Cells(i, "B").PasteSpecial xlPasteValues
Next i


i = 0


' Process the Sucrose data
For i = 1 To 72 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "C").PasteSpecial xlPasteValues
Next i

i = 0


' Process the Et Glycol Data
For i = 1 To 65 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "D").PasteSpecial xlPasteValues


Next i

i = 0


' Process the Glycerol Data
For i = 1 To 47 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "E").PasteSpecial xlPasteValues
Next i


End Sub

 

[AlphaFrog]

Hi and welcome to the forum.

Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "A").PasteSpecial xlPasteValues <---GET AN ERROR HERE

The sheet code names (Sheet6) can only be used to refernce the sheets in the workbook that contains the macro code. Sheet code names cannot be used to reference sheets in other workbooks.

Try using Sheets(6) or Sheets("Tab Name")

 

[SconnorA]

Thanks. Upon making those changes and then running the code again, I now get an error at

Sheet1.Range("C10").PasteSpecial xlPasteValues

saying, "Method 'PasteSpecial' of object 'Range' failed"

NOTE: this sheet and range is in the workbook that holds the macro

 

[AlphaFrog]

Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
        Sheet1.[COLOR=#ff0000]Columns(i).Copy[/COLOR]
        Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
        Sheet1.Range([COLOR=#ff0000]"C10"[/COLOR]).PasteSpecial xlPasteValues

Cannot copy an entire column and then paste it starting at row 10. It throws an error because the last 10 cells of the copied column have no where to go. Even if they are just empty cells.

Try copying a smaller range e.g.
Sheet1.Cells(1,i).Resize(1000).Copy

 

[SconnorA]

Thank you again for your help. One last question as my code is now working, but not as efficiently as I had planned and I know it can be improved.

Basically I want to move through the columns of the raw data from rows 1 to 640. It would be something like this;

Sheet1.Range(i1:i640).Copy

Although I know this code is not correct, it gives you the jist of what I am trying to accomplish. If this range is copied, then I would be able to just paste it to Range("C10) as the array will be short enough to fit.

 

[AlphaFrog]

Any one of these should work:
Sheet1.Cells(1, i).Resize(640).Copy
Sheet1.Columns(i).Resize(640).Copy
Sheet1.Columns(i).Range("A1:A640").Copy

Also, you don't have to .Activate a workbook. You can reference the macro workbook's sheets with just the sheet code name (Sheet1) without the workbook being active. And you can reference the other workbooks by qualifying the workbook with the sheet and range.

Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(1).Cells(1,i).Resize(640).Copy

 

[SconnorA]

I can't express how thankful I am for your help.

I am very new to this and this Macro is my first in Excel VBA. The next issue I'm having is with Solver. All the proper copy/paste/iterate portions are working flawlessly, but when the data is copied and Solver is supposed to run on this raw data Solver does not go. Because of this, I am copying the arbitrary value I entered into cell B4 to begin Solver and pasting only this in my output sheet.

I have already enabled the reference for Solver in Tools>References for the correct module.

 

[AlphaFrog]

You're welcome. You did an excellent job for your 1st macro. Well done.

Perhaps this link may help.
Using Solver in Excel VBA

Otherwise, you may want to show your updated code (please take note of my signature block below about the use of CODE tags). Or better yet, upload an example workbook to a file share site and post the link here. I'll then take a look at it.

 

[SconnorA]

#
Option Explicit


Sub CurveFit()
Dim i As Single

'Process the DMSO data
For i = 1 To 45 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(1).Cells(1, i).Resize(640).Copy
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve True
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(6).Cells(i, "A").PasteSpecial xlPasteValues
Next i
i = 1
' Process the NaCl Data
For i = 1 To 102 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(1).Cells(1, i).Resize(640).Copy
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve True
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(6).Cells(i, "B").PasteSpecial xlPasteValues
Next i
i = 1
' Process the Sucrose data
For i = 1 To 72 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(1).Cells(1, i).Resize(640).Copy
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve True
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(6).Cells(i, "C").PasteSpecial xlPasteValues
Next i
i = 1
' Process the Et Glycol Data
For i = 1 To 66 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(1).Cells(1, i).Resize(640).Copy
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve True
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(6).Cells(i, "D").PasteSpecial xlPasteValues
Next i
i = 1
' Process the Glycerol Data
For i = 1 To 48 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(1).Cells(1, i).Resize(640).Copy
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve True
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(6).Cells(i, "E").PasteSpecial xlPasteValues
Next i


End Sub
#

 

[AlphaFrog]

# icon in the forum editor menu applies

 tags around selected text. Not the # character.

If you use this...
[CODE]SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")

...the solver workbook and sheet will need to be active. Otherwise it defaults to whatever sheet is currently active which may or may not be your solver sheet.