PDA

View Full Version : [SOLVED] Stuck trying to figure out this macro



Southern
06-02-2019, 07:40 PM
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.
24324

24325

24326

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
06-03-2019, 06:39 AM
Please attach a workbook with this in it. No-one's going to try and reproduce your setup from pictures.

Dave
06-03-2019, 06:49 AM
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
06-03-2019, 04:46 PM
24345I have attached the workbook that I am working with.

Southern
06-03-2019, 04:49 PM
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
06-03-2019, 06:40 PM
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
06-03-2019, 07:21 PM
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
06-04-2019, 03:22 PM
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
06-05-2019, 11:16 AM
You are welcome. Thanks for posting your outcome. Dave