Excel VBA Subtracting one column from another, but not in a cell

Question

Sub TryAgain()
    Dim A As Range '~ I will put the result in this column, A
    Dim B As Range
    Dim C As Range '~ [B-C] is the what I need
    Dim onecell As Range
    Dim twocell As Range
    Set B = Range("C2", Range("C2").End(xlDown)) 'log prices of Z over time
    Set C = Range("D2", Range("D2").End(xlDown)) 'log prices of WPC over time
    Set A(1) = B.Value - C.Value
End Sub

I've been struggling with solving this annoying problem for a while.

This is the SIMPLE subtracting operation between two columns,

so what i want to do is conduct the subtract operation 'CELL-BY-CELL'

BUT! I don't want to put the results in cells (to be referred) or creating any other sheets.

So, I want to assign the subtraction result (i.e. C2-D2, C3-D3 ...) directly into a variable called A (this should be a column),
NOT recording on worksheet cells and referring the cells, but this is obviously very difficult to get it through.

is there any other way to do this?

Much appreciated in advance!

Solution 1

I think you are looking for an Array:

Sub TryAgain()
Dim A() As Integer '~ I will put the result in this column, A
Dim i As Integer
Dim lastrow As Integer

lastrow = Range("B2").End(xlDown).Row '<- assume there's no empty cells from C2 to last row on column C
ReDim A(lastrow)

For i = 1 To lastrow
A(i) = Cells(i + 1, 2).Value - Cells(i + 1, 3).Value
Next
' add below for loop to print out result
For j = 1 To lastrow - 1
Debug.Print A(j)
Next

End Sub

Solution 2

Here is a sample spreadsheet based on what you described:

And this code will solve the problem...to show it works there is a loop at the end for a message box to display the answers but you can use the array as shown in the code as you were planning to. I think you may want to consider the benefits of storing the data in a range but that is your design choice to make.

Sub SubtractCells()
    Dim dblTolerance As Double
    Dim tmp As Range
    Dim A() As Double
    Dim intListSize As Integer
    Dim i As Integer

    'Get source range
    Set tmp = ActiveSheet.Range("C2")

    'Get size of list by finding last row
    Do Until tmp.Offset(1, 0).Value = ""
        Set tmp = tmp.Offset(1, 0)
    Loop

    'Subtract last row from first row of data to get number of rows and add one for offset from beginning
    intListSize = (tmp.Row - 2) + 1

    ReDim A(intListSize)
    i = 1 'index for array

    'use the temporary variable to cycle through the range
    Set tmp = ActiveSheet.Range("C2")
    Do Until tmp.Value = ""
        'Subtract the two rows and store in array
        A(i) = tmp.Value - tmp.Offset(0, 1).Value

        'Update range row and index
        i = i + 1
        Set tmp = tmp.Offset(1, 0)
    Loop

    'Loop through array to see results
    'This is only to verify code works comment out and use array
    'I would rethink storing this on a spreadsheet.

    For i = 1 To intListSize
        MsgBox ("A(" & Str(i) & "):" & Str(A(i)))
    Next
    'Clean up
    Set tmp = Nothing
End Sub

Let me know if you have any questions.

Comments

[David]

You can use the Debug.print as well to see the values just change the msgbox to debug.print and remove the parens ie debug.print A(i) if you want to make it easier