Posts by ThankfulChap

ThankfulChap · May 7th 2020

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

ThankfulChap · May 7th 2020

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

Display More

Posts by ThankfulChap

Performing calculation for multiple rows from one sheet on input data on a different worksheet

Performing calculation for multiple rows from one sheet on input data on a different worksheet