Revision in code requested to avoid hanging of Excel Workbook

[hsandeep]

I am using a Function findhigh as

Function findhigh(a As Range, b As Range, c As Range) As String

In the code.
Then I am finding number of 1s in c Range using

y = Application.CountIf(c, 1)

Now the code works smoothly when

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

But the excel workbook gets hanged when

If y = 2 Then
       If a(y) = 0 Or a(y) = "" Then GoTo finish
        If b(y) = 0 Or b(y) = "" Then GoTo finish
                     If a(y - 1) > a(y) And b(y - 1) < b(y) Then
                                               findhigh = Format(a(y - 1), "0.00")
                                               GoTo finish

                      End If
End If

I am finishing the code with

findhigh = "null"
         finish:
End With
End Function

What mistake is there in the code? Any revision/help so that hanging issue of the workbook gets solved will be highly appreciated.

 

[StephenCrump]

Please post the complete code you are using for Function findhigh when it hangs.

 

[hsandeep]

Please post the complete code you are using for Function findhigh when it hangs.

The complete code

'Code finds the findhigh when the criteria are met
Function findhigh(a As Range, b As Range, c As Range) As String
    
With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With
    
'Code finds the number of appearance of "1" in the 3rd Range
y = Application.CountIf(c, 1)

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 Then
            If a(y) = 0 Or a(y) = "" Then GoTo finish
            If b(y) = 0 Or b(y) = "" Then GoTo finish
                        
                If a(y - 1) > a(y) And b(y - 1) < b(y) Then
                        findhigh = Format(a(y - 1), "0.00")
                        GoTo finish
                End If
End If

If y = 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 Then
                GoTo finish
End If

    findhigh = "null"
    
finish:

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Function

'Code finds the findlow when the criteria are met
Function findlow(a As Range, b As Range, c As Range) As String

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With

'Code finds the number of appearance of "1" in the 3rd Range
y = Application.CountIf(c, 1)

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 Then
            If a(y) = 0 Or a(y) = "" Then GoTo finish
            If b(y) = 0 Or b(y) = "" Then GoTo finish
                        
                If a(y - 1) > a(y) And b(y - 1) < b(y) Then
                        findlow = Format(b(y - 1), "0.00")
                        GoTo finish
                End If
End If

If y = 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 Then
                GoTo finish
End If


    findlow = "null"

finish:

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Function

Here also note:
a range extends from D4:M4 & then from D12:M12 till D200:M200;
b range extends from D6:M6 & then from D14:M14 till D202:M202

please notice that b range is 2 rows below the a range always for each set.

c range is confined to D10:M10

 

[bebo021999]

Not yet working with the hanging error, but from beginning:

y = Application.CountIf(c, 1)

says y = numbers if "1" found in range "c"

but next lines:

If y >= 3 Then
     If a(y) = 0 Or a(y) = "" Then GoTo finish
     If b(y) = 0 Or b(y) = "" Then GoTo finish

it seems you try to refer to a ( 3 ) and b ( 3 ) (y as position of "1" found in c)

For instant.
There are three "1" found in c (with c = "D10:M10", "1" could be anywhere, i.e, D10, E10,M10)
But, a(3) and b(3) is the third items in range (with a = "D4:M4", a(3) = cell F3 value), while you expect to get D4, E4 & M4.
Is it correct?

 

[hsandeep]

Not yet working with the hanging error, but from beginning:

y = Application.CountIf(c, 1)

says y = numbers if "1" found in range "c"

but next lines:

If y >= 3 Then
     If a(y) = 0 Or a(y) = "" Then GoTo finish
     If b(y) = 0 Or b(y) = "" Then GoTo finish

it seems you try to refer to a ( 3 ) and b ( 3 ) (y as position of "1" found in c)

For instant.
There are three "1" found in c (with c = "D10:M10", "1" could be anywhere, i.e, D10, E10,M10)
But, a(3) and b(3) is the third items in range (with a = "D4:M4", a(3) = cell F3 value), while you expect to get D4, E4 & M4.
Is it correct?

Your quote
it seems you try to refer to a ( 3 ) and b ( 3 ) (y as position of "1" found in c) CORRECT provided y=3
There are three "1" found in c (with c = "D10:M10", "1" could be anywhere, i.e, D10, E10,M10) NO IT WOULD ALWAYS BE D10=1 then D10=1 AND E10=1, then D10=1 AND E10=1 AND F10=1 & so on

 

[hsandeep]

Not yet working with the hanging error, but from beginning:

y = Application.CountIf(c, 1)

says y = numbers if "1" found in range "c"

but next lines:

If y >= 3 Then
     If a(y) = 0 Or a(y) = "" Then GoTo finish
     If b(y) = 0 Or b(y) = "" Then GoTo finish

it seems you try to refer to a ( 3 ) and b ( 3 ) (y as position of "1" found in c)

For instant.
There are three "1" found in c (with c = "D10:M10", "1" could be anywhere, i.e, D10, E10,M10)
=, a(3) and b(3) is the third items in range (with a = "D4:M4", a(3) = cell F3 value), while you expect to get D4, E4 & M4.
Is it correct?

Your quote a(3)...expect to get D4, E4 & M4 NO WITH a="D4:M4" & b="D6:M6", a(3)=cell value F4 & b(3)=cell value F6...this is what I think the code is doing, I may be wrong

 

[StephenCrump]

Here also note:
a range extends from D4:M4 & then from D12:M12 till D200:M200;
b range extends from D6:M6 & then from D14:M14 till D202:M202

please notice that b range is 2 rows below the a range always for each set.

c range is confined to D10:M10

I can't see anything in your function that would cause Excel to hang. Or more specifically, why it should hang if COUNTIF(D10:M10,1) =2, which I think is what you are saying?

What happens if you create a new workbook/worksheet with all these a, b, and c values, plus your functions for these 25 (or 26?) datasets?

 

[hsandeep]

I can't see anything in your function that would cause Excel to hang. Or more specifically, why it should hang if COUNTIF(D10:M10,1) =2, which I think is what you are saying?

What happens if you create a new workbook/worksheet with all these a, b, and c values, plus your functions for these 25 (or 26?) datasets?

I would like to tell you when Y= 2 or Y=1 the things are Excel getting hanged. as soon as Y>=3 things become normal like as if nothing as happened and the code runs smoothly thereafter
I think since I am not expert in VBA, I had made some error while writing the code for Y=2 it may need some correct loop or something else .......which I am unable to detect or explain

making of another work book is not possible

 

[hsandeep]

I can't see anything in your function that would cause Excel to hang. Or more specifically, why it should hang if COUNTIF(D10:M10,1) =2, which I think is what you are saying?

What happens if you create a new workbook/worksheet with all these a, b, and c values, plus your functions for these 25 (or 26?) datasets?

The problem persists till F10 is not equal to 1.After that code runs smoothly.
It implies that code is unable to 'adhust' itself when AND((D10=1 OR D10:E10=1),F10 is not equal to 1)
By adjusting I mean that probably the code loops sometimes into a problem....causing findhigh/findlow = #VALUE! which may be causing this hanging problem
so if the code is written in '2 parts':
First Part: When F10 is not equal to 1 then get findhigh & findlow (here Do While....n<3.....Loop)
Second Part: When F10=1 then get all findhigh & findlow (here Do While....n>=3....Loop)...this second part is working perfectly well but if you want to change the code you may change it.

 

[bebo021999]

I guess 90% hanging possibility is from Do...Loop
It could help a lot if you can post a picture/XL2BB mini sheet, or attach a sample file via google drive