How do I output a value without placing it into the worksheet?-CodePudding

I have a pretty simple Sub that contains the following code -

Sub UnitNetCheck()
    Dim UnitValue As Integer
    Dim NetArea As Long

    If Not [COUNTA(F2:F250)=0] Then
        For UnitValue = 2 To 250
            Cells(UnitValue, 26) = Cells(UnitValue, 4) * Cells(UnitValue, 6)
            If Cells(UnitValue, 26) = 0 Then
                Cells(UnitValue, 26).Value = ""
            Else
            End If
        Next
    Else
        NetArea = Cells(Rows.Count, 7).End(xlUp).Row
        Worksheets("Sheet1").Range("Z2:Z" & NetArea).Value = 
            Application.Transpose(Worksheets("Sheet1").Range("G2:G250").Value)
    End If
End Sub

This sub UnitNetCheck, right now all it does is multiply some cells with some other cells and places the values in Z2:Z250.

Instead of it outputting the values into the specific range noted, I would like to instead use the output in other code without it populating on the worksheet.

What I would like to do is later use the sub as part of a worksheet SUMIF function as Sumif("defined range")("defined criteria")(UnitNetCheck Sub).

CodePudding user response：

To save the data for later use in code, you need to create a module level variable (like a global in other languages) that is an array type.

First step is to create the array, so from the VBA Editor menu, select Insert->Module, then past the following:

'Declare the array inside a module file
Dim CellData(250)

Next step is to save the data from your existing code, like this:

For UnitValue = 2 To 250
Cells(UnitValue, 26) = Cells(UnitValue, 4) * Cells(UnitValue, 6)
    If Cells(UnitValue, 26) = 0 Then
    Cells(UnitValue, 26).Value = ""
    Else
        'Save in array for later use
        CellData(UnitValue) = Cells(UnitValue, 26).Value
    End If
Next