VBA - Using IsEmpty to check a range of cells

[rickyyy2006]

Sub ShowStockWithRisingPotential()

    Dim sheet As Worksheet
    
    Dim CurrentRowNumber As String
    
    Dim RisingValue As Double, RisingThershold As Double, RisingTrend As Double, Result As Variant, i As Integer, j As Integer, Stock As Long
   
    CurrentRowNumber = Application.InputBox("Row Number:")
    
    For Each sheet In Worksheets
        
        With sheet
            
            j = 0
            
            For i = 0 To 30 Step 7
                
                RisingValue = .Cells(Rows.Count, i + 2).End(xlUp).Value
                RisingThershold = .Cells(Rows.Count, i + 3).End(xlUp).Value
                RisingTrend = .Cells(Rows.Count, i + 4).End(xlUp).Value
                
                ResultRTSRV = RisingThershold - RisingValue
                ResultRTRV = RisingTrend - RisingVaule
                ResultRTRTS = RisingTrend - RisingThershold
                Stock = .Cells(1, i + 2).Value


                
[COLOR=#ff0000][B]                If IsEmpty(Range(.Cells(4, i + 4), .Cells(100, i + 4))) = True Then[/B][/COLOR]
[COLOR=#ff0000][B]                    'When Rising Trend is empty[/B][/COLOR]
[COLOR=#ff0000][B]                    If (ResultRTSRV > 0 And ResultRTSRV < 0.1 And IsEmpty(.Cells(CurrentRowNumber, i + 4)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 5)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 6)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 7)) = True) Then[/B][/COLOR]
[COLOR=#ff0000][B]                        MsgBox (sheet.Name & ":" & Stock)[/B][/COLOR]
[COLOR=#ff0000][B]                    Else[/B][/COLOR]
[COLOR=#ff0000][B]                        MsgBox ("Use Next Method")[/B][/COLOR]
[COLOR=#ff0000][B]                    End If[/B][/COLOR]
[COLOR=#ee82ee]                ElseIf IsEmpty(Range(.Cells(4, i + 4), .Cells(100, i + 4))) = False Then[/COLOR]
[COLOR=#ee82ee]                    'When Rising Trend is not empty[/COLOR]
[COLOR=#ee82ee]                   If (ResultRTRTS > 0 And ResultRTRTS < 0.1 And IsEmpty(.Cells(CurrentRowNumber, i + 2)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 5)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 6)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 7)) = True) Then[/COLOR]
[COLOR=#ee82ee]                            MsgBox (sheet.Name & ":" & Stock)[/COLOR]
[COLOR=#ee82ee]                           [/COLOR]
[COLOR=#ee82ee]                    ElseIf (ResultRTRV > 0 And ResultRTRV < 0.1 And IsEmpty(.Cells(CurrentRowNumber, i + 3)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 5)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 6)) = True And IsEmpty(.Cells(CurrentRowNumber, i + 7)) = True) Then[/COLOR]
[COLOR=#ee82ee]                            MsgBox (sheet.Name & ":" & Stock)[/COLOR]
                    End If
            
                End If
            
            Next i
        
        End With
        
    Next sheet
    
End Sub

Dear All

After i completed this code, i found that the red part is wrong so it always comes up with False and run the purple part only.

How can i fix red part?

The red part wants to check if cells from cell (4,4) to cell(100,4) in the first sheet are empty or not. If yes, then go to the underlying if statement.
Then, it checks cells from cell(4,11) to cells(100,11) in the first sheet are empty or not. If yes, then go to the underlying if statement.
The above action loops till the end of the workbook

Thanks

 

[rickyyy2006]

Moreover, why am i wrong? Cause I think my coding and logic are correct. Many thanks if anyone can help me

 

[MickG]

You would be better to use worksheet function "CountA" , to check multiple cells. If it returns 0 the range is empty !!!

 

[bhos123]

If WorksheetFunction.CountBlank(Range(Cells(4, i+4), Cells(100, i+4))) = 97 Then

 

[rickyyy2006]

You would be better to use worksheet function "CountA" , to check multiple cells. If it returns 0 the range is empty !!!

Thank you so much.It is great help

 

[rickyyy2006]

If WorksheetFunction.CountBlank(Range(Cells(4, i+4), Cells(100, i+4))) = 97 Then

Thank you so much.It is great help

 

[MickG]

You're welcome