So:
Writing code as
ws1.Range("B" & rng.Row).Copy Destination:=ws2.Range("B6")
Now it is working. However still not getting result.
Mainly due to
1. When the results are being copied from WS3 sheet to WS1, it is actually copying formula instead of actual value
2. Secondly The loop appears to drop out after first row Any additional assistance will be appreciated
ANy help would be appreciated
Posts by ThankfulChap
-
-
I have a dataset on Worksheet "Results" and the dataset is in cells B8:K900 (Actual data in these cells, rest all cells have other meta information this dataset)
Each column of this data set refers to a certain variable like B column has Steam flow, C column has Steam temp etc.
I would like to use these values to plug into a calculator on another sheet, Row by row.
Eventually I will have results in 2 Cells on another sheet which I would like to bring back to Column L and M
Code so far:
Note: Goal Seek and Rerun are 2 macros which I would like run after one row of Scenario is in input, as these will help to get results. Hence I have added them in loop also
Any help would be appreciated.
Code- Sub Goal_Seek()
- Range("I33").GoalSeek Goal:=Range("P33").Value, ChangingCell:=Range("E3")
- End Sub
- Sub Rerun()
- Do Until Range("P20") = Range("P22").Value Range("P20") = Range("P22").Value
- Loop
- End Sub
- Sub Calcs()
- Dim wb As Workbook
- Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet
- Dim rng As Range
- Set wb = ActiveWorkbook
- Set ws1 = wb.Worksheets("Results")
- Set ws2 = wb.Worksheets("PI Data")
- Set ws3 = wb.Worksheets("Mole_avg")
- Set ws4 = wb.Worksheets("eff")
- Set ws5 = wb.Worksheets("FlueGas")
- Set rng = ws1.Range("B8:M9") For Each Row In rng.Rows ws1.Range("B") = ws2.Range("B6").Value ws1.Range("C").Copy Destination:=ws2.Range("B3") ws1.Range("D").Copy Destination:=ws2.Range("B4") ws1.Range("E").Copy Destination:=ws2.Range("B5") ws1.Range("F").Copy Destination:=ws2.Range("B6") ws1.Range("G").Copy Destination:=ws2.Range("B7") ws1.Range("H").Copy Destination:=ws2.Range("B8") ws1.Range("I").Copy Destination:=ws2.Range("B9") ws1.Range("J").Copy Destination:=ws2.Range("B10") ws1.Range("K").Copy Destination:=ws2.Range("B11")
- Application.Run "Goal_Seek" Application.Run "Rerun"
- ws3.Range("P17").Copy Destination:=ws1.Range("L") ws3.Range("T22").Copy Destination:=ws1.Range("M") Next Row
- End Sub