With lot of ‘googling’ I have
REVISED the ‘new vba code’ in
Module in the
Worksheet (tab named “ZZZ”) using User Defined Function to get outputs in some cells where UDFs are there. Same Workbook which has several ws & some ws with
Worksheet codes (28 ws with code precisely). In ‘
ThisWorkbook’ also I am using a macro.
Codes were running fine in all the ws till I inserted this
new Worksheet tab named “ZZZ” (in this same Workbook) & in the
Module I pasted this ‘new vba code’.
Since the addition of ‘new vba code’, I am facing ‘troubling issues’ with my Workbook getting ‘hanged’, Worksheets flickering & the arrow cursor turning in to a blue spinning ball. The astonishing part of this new vba code 's execution is that the 'troubling issues'
remains for a specified time period I.e it starts when now>= 08-09-2022 9:15:00 AM & remains up to now<08-09-2022 10:00:00 AM i.e. from
9:15 till 10:00; whenever the Workbook is kept opened. As soon as now>=10:00, all these ‘troubles’ vanishes & the all ws in the Workbook functions normal including this
new Worksheet tab named “ZZZ”.
The problem
PERSISTS ONLY WHEN D10:E10=1 & as soon as
F10=1, ALL PROBLEMS VANISH.
The problem ‘identified’ is
ALL T COLUMNS BECOME #VALUE! even when the conditions are met (when
E10=1). Here I would definitely say, that I had observed that ‘sometimes’ column T populated
desired values when E10 was “1” for
FEW SECONDS but again some/all ‘function cells’ became
#VALUE!
My guess: When D10=1 code finds y=1; & thereafter when E10=1 code finds y=2. The trouble starts &
PERSISTS ONLY WHEN D10:E10=1.
AFTER>=10:00 I.E. WHEN F10=1, EVERY ACTIONS BECOME ‘NORMAL’.
The a Range & all b Range (in this
new Worksheet tab named “ZZZ”) are D4:M4 & D6:M6; similarly D12:M12 & D14:M14……& so on till D40:M40 & D42:M42 &
they can generate “
0” zero OR a numerical value only & not '' (null) or space/(s)
D10:M10 becomes 1 from 0. By default D10:M10=0 & time function in another worksheet to which they (D10:M10) are ‘linked’ using simple Excel function, make them “1”;
one by one. Example: D10=Time!C32, E10=Time!C56……& so on.
I am using a Function findhigh & findlow in this
new Worksheet tab named “ZZZ” & the complete
REVISED code is
Rich (BB code):
'Code finds the findhigh when the criteria are met
Rich (BB code):
Function findhigh(a As Range, b As Range, c As Range) As String
Dim x As Integer
x = Application.Sheets("ZZZ").Range("D10:M10").Cells.Count
Dim y As Integer
y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
If y = x And Sheets("ZZZ").Range("WA10").Value = "=1" Then
GoTo finish
End If
If y >= 3 Then
If a(y) = 0 Or a(y) = "" Then GoTo finish
If b(y) = 0 Or b(y) = "" Then GoTo finish
Do While y > 1
hv = a(y): lv = b(y)
For q = y - 1 To 1 Step -1
If a(q) > hv And b(q) < lv Then
findhigh = Format(a(q), "0.00")
GoTo finish
End If
y = y - 1
Next q
Loop
End If
If y = 2 And Sheets("ZZZ").Range("F10").Value < 1 Then
If a(y) = "" Then GoTo finish
If b(y) = "" Then GoTo finish
If a(y) = 0 Or b(y) = 0 Then
findhigh = Format(a(y - 1), "0.00")
GoTo finish
End If
q = y - 1
Do While q <> 0
hv = a(y): lv = b(y)
For q = y - 1 To 1 Step -1
If a(q) > hv And b(q) < lv Then
findhigh = Format(a(q), "0.00")
GoTo finish
End If
y = y - 1
Next q
Loop
End If
If y = 1 And Sheets("ZZZ").Range("E10").Value < 1 Then
If a(y) = 0 Or a(y) = "" Then GoTo finish
If b(y) = 0 Or b(y) = "" Then GoTo finish
findhigh = Format(a(y), "0.00")
GoTo finish
End If
If y = 0 And Sheets("ZZZ").Range("D10").Value < 1 Then
GoTo finish
End If
findhigh = "null"
finish:
End Function
'Code finds the findlow when the criteria are met
Function findlow(a As Range, b As Range, c As Range) As String
Dim x As Integer
x = Application.Sheets("ZZZ").Range("D10:M10").Cells.Count
Dim y As Integer
y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
If y = x And Sheets("ZZZ").Range("WA10").Value = "=1" Then
GoTo finish
End If
If y >= 3 Then
If a(y) = 0 Or a(y) = "" Then GoTo finish
If b(y) = 0 Or b(y) = "" Then GoTo finish
Do While y > 1
hv = a(y): lv = b(y)
For q = y - 1 To 1 Step -1
If a(q) > hv And b(q) < lv Then
findlow = Format(b(q), "0.00")
GoTo finish
End If
y = y - 1
Next q
Loop
End If
If y = 2 And Sheets("ZZZ").Range("F10").Value < 1 Then
If a(y) = "" Then GoTo finish
If b(y) = "" Then GoTo finish
If a(y) = 0 Or b(y) = 0 Then
findlow = Format(b(y - 1), "0.00")
GoTo finish
End If
q = y - 1
Do While q <> 0
hv = a(y): lv = b(y)
For q = y - 1 To 1 Step -1
If a(q) > hv And b(q) < lv Then
findlow = Format(b(q), "0.00")
GoTo finish
End If
y = y - 1
Next q
Loop
End If
If y = 1 And Sheets("ZZZ").Range("E10").Value < 1 Then
If a(y) = 0 Or a(y) = "" Then GoTo finish
If b(y) = 0 Or b(y) = "" Then GoTo finish
findlow = Format(b(y), "0.00")
GoTo finish
End If
If y = 0 And Sheets("ZZZ").Range("D10").Value < 1 Then
GoTo finish
End If
findlow = "null"
finish:
End Function
For another 12 ws, I am using the below code pasted in
ThisWorkbook:
Rich (BB code):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Delete data in F2:G42 if cell A2="z" The code triggers ONLY when the file is clicked SAVED
'Then the code FILLS BLANKS in F2:G42 by removing the EXISTING VALUES if any from the worksheets
'tab named H001:H9
'Declare the variables
Dim ary As Variant, i As Long
ary = Array(Sheet86, Sheet83, Sheet82, Sheet81, Sheet80, Sheet39, Sheet36, Sheet33, Sheet42, Sheet77, Sheet78, Sheet79)
For i = 0 To UBound(ary)
If ary(i).Range("A2") = "z" Then ary(i).Range("F2:G42").ClearContents
Next i
End With
End Sub
EARNEST REQUEST TO ALL GURUS OF THIS FORUM TO HELP & SOLVE THE TROUBLE SINCE THE PROBLEM STILL
PERSISTS ONLY WHEN D10:E10=1 & AS SOON AS
F10=1, ALL PROBLEMS VANISH.
I HAD TRIED MY BEST BY REVISING THE CODE AS FAR AS I COULD
