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.
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
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!
You are welcome. Thanks for posting your outcome. Dave
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.