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