tesal
03-25-2014, 10:10 AM
Hi everyone!
I have a worksheet with several variables depending on the first column -the time. I want to input the time and for that I'm using a counter (t = t+1) but when some restrictions are met I need to calculate a different time point that will change the system (for that I'm using a goal seek function). After that I want the time in the next row to be the near whole number after the new time calculates but I always get an error. If I run only the goalseek part of the code it runs ok, but if I complete it with the roundup function it says that there is a reference problem in the goalseek function. I really don't know what I'm doing wrong.
Sub time()
Dim myworksheet As Worksheet
Dim t As Long
Dim j As Long
Dim newtime As Long
Dim result As Long
j = 10
t = 0
Do While t <= 24
Cells(j, 23).Value = t
If Cells(j, 27).Value < 0.5 Or Cells(j, 27).Value > 5 Then
Cells(j, 24).GoalSeek Goal:=1E-16, ChangingCell:=Cells(j, 23)
End If
j = j + 1
result = Application.WorksheetFunction.RoundUp(Cells(j - 1, 23), 0)
newtime = Cells(j, 23).Value
If result > newtime Then
t = result
ElseIf result <= newtime Then
t = t + 1
End If
Loop
End Sub
I have a worksheet with several variables depending on the first column -the time. I want to input the time and for that I'm using a counter (t = t+1) but when some restrictions are met I need to calculate a different time point that will change the system (for that I'm using a goal seek function). After that I want the time in the next row to be the near whole number after the new time calculates but I always get an error. If I run only the goalseek part of the code it runs ok, but if I complete it with the roundup function it says that there is a reference problem in the goalseek function. I really don't know what I'm doing wrong.
Sub time()
Dim myworksheet As Worksheet
Dim t As Long
Dim j As Long
Dim newtime As Long
Dim result As Long
j = 10
t = 0
Do While t <= 24
Cells(j, 23).Value = t
If Cells(j, 27).Value < 0.5 Or Cells(j, 27).Value > 5 Then
Cells(j, 24).GoalSeek Goal:=1E-16, ChangingCell:=Cells(j, 23)
End If
j = j + 1
result = Application.WorksheetFunction.RoundUp(Cells(j - 1, 23), 0)
newtime = Cells(j, 23).Value
If result > newtime Then
t = result
ElseIf result <= newtime Then
t = t + 1
End If
Loop
End Sub