Stuck trying to figure out this macro

[Southern]

Hello,

I am working on a bit of a side project for my place of work. I am trying to create a new system information area that we use throughout the day, mainly hourly but sometimes more than that.
What I have set up is a macro that runs when the "next" button is clicked. When the "next" button is clicked it changes the current letter in cell A3 to the next letter and, at the same time, changes the hourly time in cell A20 to the next hourly time.
tyrant ava_zpsbt8an2z4.jpg

Annotation 2019-06-02 215338.jpg

Annotation 2019-06-02 215508.jpg

Everything works great as long as the hourly times in cell A20 match the values in cells I75:I88. As soon as we have to update the information manually in cell A20 outside of the hourly time (I75:I88) I.E. 1123 the current Error protocol defaults the value to I75=0053 when the "next" button is clicked. What I am trying to figure out is how to change it to have it cycle through the hourly times (I75:I88) as normal. But also if we need to update prior to the hourly time (I.E. 1123) when the next button is clicked it would cycle to the next hourly time (I.E. 1123 would be 1153 once the "next" button is clicked)

If anyone has any ideas, your help would be greatly appreciated.

[p45cal]

Please attach a workbook with this in it. No-one's going to try and reproduce your setup from pictures.

[Dave]

Untested but this looks like it might work...

Function NextHourTime(YourInput As Double) As Double
Dim Cnt As Integer
With ActiveSheet
If Application.WorksheetFunction.CountIf(.Range(.Cells(75, "I"), .Cells(88, "I")), _
        YourInput) = 0 Then
For Cnt = 75 To 88
If .Range(.Cells(Cnt, "I")) > YourInput Then
NextHourTime = .Range(.Cells(Cnt, "I"))
Exit Function
End If
Next Cnt
End If
End With
NextHourTime = YourInput
End Function

To operate...

.Range("A20").Value = NextHourTime(.Range("A20").Value)

HTH. Dave

[Southern]

Attachment 24345I have attached the workbook that I am working with.

[Southern]

I tried this and I could not figure out how to get this one to work. I keep getting "invalid or unqualified result" It's very possible its just me. I'm not very good with VBA. I have now attached the workbook that I am working on though.

Thanks

[大灰狼1976]

Hi Southern!

Sub RunAllMacros()
Procedure1
Procedure2
End Sub

Sub Procedure1()
Dim n&
n = Asc([a3])
If n = 90 Then n = 65 Else n = n + 1
[a3] = Chr(n)
End Sub

Sub Procedure2()
Dim rng As Range, s$
Set rng = [I75:I88].Find([a20], lookat:=xlWhole)
If Not rng Is Nothing Then s = rng.Offset(1)
If s = "" Then s = [i75]
[a20] = s
End Sub

[Dave]

Maybe you're already answered up? U can also trial this...

Sub Procedure2()
If Sheets("sheet1").Range("A20").Value = Sheets("sheet1").Range("I" & 88).Value Then
Sheets("sheet1").Range("A20").Value = CStr(Sheets("sheet1").Range("I75").Value)
Exit Sub
End If
Sheets("sheet1").Range("A20").Value = NextHourTime(Sheets("sheet1").Range("A20").Value)
Sheets("sheet1").Range("A20").Value = CStr(Sheets("sheet1").Range("I75").offset _
(Application.Match(CStr(Sheets("sheet1").Range("A20").Value), Sheets("sheet1").Range("I75:I88"), 0), 0).Value)
End Sub
Function NextHourTime(YourInput As String) As String
Dim Cnt As Integer
With Sheets("sheet1")
If Application.WorksheetFunction.CountIf(.Range(.Cells(75, "I"), .Cells(88, "I")), _
        YourInput) = 0 Then
For Cnt = 75 To 88
If .Range("I" & Cnt) > YourInput Then
NextHourTime = .Range("I" & Cnt - 1)
Exit Function
End If
Next Cnt
End If
End With
NextHourTime = YourInput
End Function

Dave

[Southern]

Maybe you're already answered up? U can also trial this...

Sub Procedure2()
If Sheets("sheet1").Range("A20").Value = Sheets("sheet1").Range("I" & 88).Value Then
Sheets("sheet1").Range("A20").Value = CStr(Sheets("sheet1").Range("I75").Value)
Exit Sub
End If
Sheets("sheet1").Range("A20").Value = NextHourTime(Sheets("sheet1").Range("A20").Value)
Sheets("sheet1").Range("A20").Value = CStr(Sheets("sheet1").Range("I75").offset _
(Application.Match(CStr(Sheets("sheet1").Range("A20").Value), Sheets("sheet1").Range("I75:I88"), 0), 0).Value)
End Sub
Function NextHourTime(YourInput As String) As String
Dim Cnt As Integer
With Sheets("sheet1")
If Application.WorksheetFunction.CountIf(.Range(.Cells(75, "I"), .Cells(88, "I")), _
        YourInput) = 0 Then
For Cnt = 75 To 88
If .Range("I" & Cnt) > YourInput Then
NextHourTime = .Range("I" & Cnt - 1)
Exit Function
End If
Next Cnt
End If
End With
NextHourTime = YourInput
End Function

Dave

This worked for me perfect! Thank you for the help!

[Dave]

You are welcome. Thanks for posting your outcome. Dave