VBA - Rounding Goal Seek result

[tesal]

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

[david000]

Welcome to VBAX

If you could upload a sample workbook, that would be great. Try using MRound instead of RoundUp and see what happens.

Or something like this:

Format(Application.WorksheetFunction.MRound(Cells(1, 1), "00:30"), "hh:mm") 'This rounds a time up a half hour in cell A1 as an example

[Paul_Hossler]

Also, if you use the [#] button on the toolbar, you can paste code between the [ CODE ] and [ /CODE ] markers

Makes it easier to read

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

Paul