Data Processing Question

StoffStoff

New member
Joined
Jan 11, 2015
Messages
7
Reaction score
0
Points
0
Hello Everyone,

I want to process 3 variables to give me 1 output. These 3 variables will be processed several times to generate that particular output.

Input data x, y , z gets processed by Process 1 and 2 to generate output F. How could I process the x,y,z data without having to reenter each x,y,z data set to calculate F ?

This is just an example , the actual problem has over 8000 data sets and about 12 elaborate processes and I just dont have the time to enter so many data points. What would be the quickest and most efficient of processing the data without rearranging the actual process setup?

I have attached the excel file to the thread. Any help would be appreciated.
 

Attachments

  • functiontestpic.jpg
    functiontestpic.jpg
    92.1 KB · Views: 15
  • FunctionTest.xlsx
    12.9 KB · Views: 21
Code:
Public Sub GetOutput()Dim lastrow As Long
Dim i As Long


    Application.ScreenUpdating = False
    
    With ActiveSheet.Range("G3")
    
        lastrow = .End(xlDown).Row
        For i = 2 To lastrow - 2
        
            ActiveSheet.Range("D3").Value = .Cells(i, 1).Value
            ActiveSheet.Range("D4").Value = .Cells(i, 2).Value
            ActiveSheet.Range("D5").Value = .Cells(i, 3).Value
            .Cells(i, 4).Value = ActiveSheet.Range("D8").Value
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub
 
Code:
Public Sub GetOutput()Dim lastrow As Long
Dim i As Long


    Application.ScreenUpdating = False
    
    With ActiveSheet.Range("G3")
    
        lastrow = .End(xlDown).Row
        For i = 2 To lastrow - 2
        
            ActiveSheet.Range("D3").Value = .Cells(i, 1).Value
            ActiveSheet.Range("D4").Value = .Cells(i, 2).Value
            ActiveSheet.Range("D5").Value = .Cells(i, 3).Value
            .Cells(i, 4).Value = ActiveSheet.Range("D8").Value
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub

Thank you very much , this worked like a charm! I tried to adapt it to the actual, more complex problem but i could not make it work. There the output is found through a "goal seek" and another subroutine in this case.
 
Hi StoffStoff, if you just wanted a simple formula to put in the Output column you could use:-
Code:
=((G4+H4)*2*I4+1)*2

*Formula relates to J4 of your example sheet.

Could you list the 12 processes?
I just combined your 3 sample processes into one, maybe we could do the same with the others.
 
Last edited:
Data Processing Problem

I attached the file in question:

Gh (D67 -> B15 ), Temp (E67-> B17) , Ambient Pressure (F67-> B16) are the inputs and Electric Power (B59-> G67) is the output. The electric Power Output has to be calculated for each of the 8760 (hourly) data sets. The processes are all listed. As you can see , the process is rather involved. I wished i could just drag the data down but what Bob Phillips sent, will most likely be what has to be done here. Any further assistance would be greatly appreciated. Thanks again !
 

Attachments

  • SC_1.xlsm
    431.4 KB · Views: 14
Last edited:
Agreed, Bob Phillips is the man :)
I've haven't done very much with macros and I don't usually like to modify other peoples macros or formulas and resubmit them, but I wanted to see if I could make this work.
Really hoping not to offend Bob by jumping on this before he had a chance (obviously he'd have no problem doing this himself and a hell of a lot quicker then it took me).
But here is my "fix".........hopefully

Code:
Public Sub GetOutput()
Dim lastrow As Long
Dim i As Long

Application.ScreenUpdating = False
    
    With ActiveSheet.Range("D66")
    
        lastrow = .End(xlDown).Row
        For i = 2 To lastrow - 65
        
            ActiveSheet.Range("B15").Value = .Cells(i, 1).Value
            ActiveSheet.Range("B16").Value = .Cells(i, 3).Value
            ActiveSheet.Range("B17").Value = .Cells(i, 2).Value
            .Cells(i, 4).Value = ActiveSheet.Range("B59").Value
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub
 
Hello Beamer and hello Bob,


The script generated an output but the calculation result was not correct. The electrical power output should be fairly proportional to the Gh values. It should more or less represent a bell curve. Day, Month and Hour are not being used by the program anywhere. the Only inputs are Gh, Temp and Ambient Pressure. The issue that most likely that does not make it work properly is the "goal seek" process to obtain the Electrical power output. It is setup so the goal seek has to be used at every step.
 
It is setup so the goal seek has to be used at every step.

If you were doing this manually, you would enter the required values into B15, B16 and B17 and then what before moving on to the next set of values ?
 
If you were doing this manually, you would enter the required values into B15, B16 and B17 and then what before moving on to the next set of values ?

I would click on calculate (goal seek) to process B15, B16, B17 and get the electric power Pel. I am totally stuck at this point. I could just enter all those values manually and process each set one by one but that would take way too long.
 
I would click on calculate (goal seek) to process B15, B16, B17
that's the part that's missing, try adding
Code:
Call CalculateChimney
right above
Code:
.Cells(i, 4).Value = ActiveSheet.Range("B59").Value
 
Introducing GoalSeek into the code is problematical, as soon as you hit a Gh value of 0, as in the first instance, GoalSeek fails, so the code fails. How should that be handled?
 
Hello NoS,

I get an error saying "Run Time Error 1004: Reference is not valid" when i attempt to run the sub macro with the "Call CalculateChimney" right above ".Cells(i, 4).Value = ActiveSheet.Range("B59").Value" in the Sub GetOutput.

this is the CalculateChimney Sub:

Sub CalculateChimney()
'
' CalculateChimney Macro
'
'
Range("B61").Select
Range("B61").GoalSeek Goal:=0, ChangingCell:=Range("B30")

End Sub
 
Introducing GoalSeek into the code is problematical, as soon as you hit a Gh value of 0, as in the first instance, GoalSeek fails, so the code fails. How should that be handled?

If i add 0,001 to the Gh input value in B15, GoalSeek seems to be ok. Could that be added to Sub GetOutput ?
 
Back
Top