Variable inserts BLANK instead of ZERO.

[S.H.A.D.O.]

Good evening,

The below code inserts nothing instead of a ZERO if there are NO results produces. It works fine if there are results produced . . .

    Set Cel = Range("M8")
    If DD = 0 Then Exit Sub
    Cel.Resize(DD) = Results
    Cel.Resize(DD).Offset(, 1) = 0
    Cel.Offset(-2, 1) = _
        Application.CountIf(Range("N8:N" & Cells(Rows.Count, "N").End(xlUp).Row), "<>")
    Cel.Offset(-2, 1).Value = Format(Cel.Offset(-2, 1).Value, "#,##0")

Thanks in advance, Paul.

 

[StephenCrump]

The below code inserts nothing instead of a ZERO if there are NO results produces. It works fine if there are results produced . . .

Do you mean this line specifically:

Cel.Resize(DD) = Results

How are you declaring and populating Results? Perhaps Results is variant and you haven't set any value(s), including zero?

And by the way, Cel.Resize(DD).Offset(, 1) = 0 will overwrite N8 and below, i.e. at least part of the range to which you later apply Countif. Is that what you intended?

 

[S.H.A.D.O.]

Thanks for the reply StephenCrump, it is appreciated.

Here is the full code . . .

Option Explicit

Const nMax As Integer = 40
Const SepChar As String = " "
Dim i As Integer, j As Integer
Dim Comb2() As Integer
Dim Cel As Range, CelVals, Results()
Dim DrawDate As Long

Sub Doubles_Excl_Bonus()
    ReDim Results(Application.WorksheetFunction.Combin(nMax, 2), 1)
    ReDim Comb2(nMax, nMax)
    
    Set Cel = Range("D8")
    DrawDate = 0 ' Added by me.
    Do
        DrawDate = DrawDate + 1
    Loop Until Cel.Offset(DrawDate).Value = ""

    Set Cel = Range(Cel, Cel.Offset(DrawDate - 1)).Offset(, 1).Resize(, 6)
    CelVals = Cel.Value
    For i = LBound(CelVals) To UBound(CelVals)
        Update Comb2(CelVals(i, 1), CelVals(i, 2))
        Update Comb2(CelVals(i, 1), CelVals(i, 3))
        Update Comb2(CelVals(i, 1), CelVals(i, 4))
        Update Comb2(CelVals(i, 1), CelVals(i, 5))
        Update Comb2(CelVals(i, 1), CelVals(i, 6))
        Update Comb2(CelVals(i, 2), CelVals(i, 3))
        Update Comb2(CelVals(i, 2), CelVals(i, 4))
        Update Comb2(CelVals(i, 2), CelVals(i, 5))
        Update Comb2(CelVals(i, 2), CelVals(i, 6))
        Update Comb2(CelVals(i, 3), CelVals(i, 4))
        Update Comb2(CelVals(i, 3), CelVals(i, 5))
        Update Comb2(CelVals(i, 3), CelVals(i, 6))
        Update Comb2(CelVals(i, 4), CelVals(i, 5))
        Update Comb2(CelVals(i, 4), CelVals(i, 6))
        Update Comb2(CelVals(i, 5), CelVals(i, 6))
    Next i
    
    DrawDate = LBound(Results) - 1
    For i = 1 To nMax - 1
        For j = i + 1 To nMax
            DoEvents ' [B][COLOR=rgb(184, 49, 47)]<================== Do I need this?[/COLOR][/B]
            If Comb2(i, j) = 0 Then
                DrawDate = DrawDate + 1
                Results(DrawDate, 1) = _
                    Format(i, "00") & SepChar & _
                    Format(j, "00")
            End If
        Next j
    Next i

    Set Cel = Range("M8")
    If DrawDate = 0 Then Exit Sub
    Cel.Resize(DrawDate) = Results
    Cel.Resize(DrawDate).Offset(, 1) = 0
    Cel.Offset(-2, 1) = _
        Application.CountIf(Range("N8:N" & Cells(Rows.Count, "N").End(xlUp).Row), "<>")
    Cel.Offset(-2, 1).Value = Format(Cel.Offset(-2, 1).Value, "#,##0")
End Sub

It works great unless there are no results. I just want it to put zero in this situation instead of leaving the cell empty!
Thanks in advance, Paul.

 

[StephenCrump]

It works great unless there are no results. I just want it to put zero in this situation instead of leaving the cell empty!

Are you saying you want a zero in M28 if the Results array is empty, i.e. if all values of Comb2() are non-zero?

If so, you could just insert:

cel.Value = 0 after your existing line Set Cel = Range("M8")

Incidentally, I assume you're using Option Base 1. If not, then DrawDate will be -1 if there are no results, and the last line here will error:

ReDim Results(Application.WorksheetFunction.Combin(nMax, 2), 1)
'...
DrawDate = LBound(Results) - 1
'...
If DrawDate = 0 Then Exit Sub
Cel.Resize(DrawDate) = Results

Better to declare both upper and lower bounds:

ReDim Results(1 To Application.Combin(nMax, 2), 1 To 1)