VBA or Formula to Subtract Fixed Value from Another Column for Huge Data

[arielina]

Hi,

I have a spreadsheet where for each value in column B (eg B2), I need to subtract it from each of the next 10 value in column A (A3-A12) and reflect each answer in column C (C3-12). I will also need to repeat the same for the next value in column B (B3) and reflect the answers in column D.

This will continue till cell B10000 . So the number of columns to house the subtracted values will go to 10,000 too. I think Excel should be able to have more than 10,000 columns.

Is there a VBA or formula to do this?

Book1.xlsx
	A	B	C	D	E	F
1	In	Out
2	6521	215
3	2532	645	2317
4	3265	342	3050	2620
5	3652	634	3437	3007	3310
6	4684	121	4469	4039	4342	4050
7	5665	342	5450	5020	5323	5031
8	5522	32	5307	4877	5180	4888
9	5536	364	5321	4891	5194	4902
10	3215	634	3000	2570	2873	2581
11	6621	53	6406	5976	6279	5987
12	1232	24	1017	587	890	598
13	5131	24		4486	4789	4497
14	2132	78			1790	1498
15	5612	32			5270	4978
16	9533	342				8899
17	1253	35
18	6521	35
19	2532	12
20	3265	324
21	3652	352
22	4684	215
Sheet3

Cell Formulas
Range	Formula
C3:C12	C3	=A3-B$2
D4:D13	D4	=A4-B$3
E5:E15	E5	=A5-B$4
F6:F16	F6	=A6-B$5

 

[arielina]

OK, I am not sure why you didn't show us what you really wanted from the start, seeing as how you even went through all the trouble of posting an example in your original post.
But try this:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    Dim c As Long
   
    Application.ScreenUpdating = False
   
'   Set first column to paste to (col "C" is column number 3)
    c = 3
   
'   Find last row with data in column B
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through all rows starting with row 2
    For r = 2 To lr
'       Populate formula
        Range(Cells(r + 1, c), Cells(r + 10, c)).Formula = "=(A" & r + 1 & "-B$" & (c - 1) & ")/B$" & (c - 1)
'       Format cells
        Range(Cells(r + 1, c), Cells(r + 10, c)).NumberFormat = "0.0%"
'       Increment column counter by 1
        c = c + 1
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Sorry for not thinking it through initially and your VBA works perfectly! Thank you very much!

 

[arielina]

Here is an array version I came up with that is very fast

Sub arielina_V1()
'
    Dim StartTime               As Double
    StartTime = Timer
'
    Dim ArrayColumn             As Long
    Dim ArrayRow                As Long
    Dim ColumnARow              As Long
    Dim ColumnBRow              As Long
    Dim LoopCount               As Long
    Dim ColumnANumbersArray     As Variant
    Dim ColumnBNumbersArray     As Variant
    Dim ResultArray()           As Variant
    Dim TempValue               As Variant
'
    ColumnANumbersArray = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    ColumnBNumbersArray = Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row - 10)
'
'  Check if ResultArray is an array
    If Not IsArray(ColumnBNumbersArray) Then
'  Convert inputVariable to a 2D array
        TempValue = ColumnBNumbersArray
        ReDim ColumnBNumbersArray(1 To 1, 1 To 1)
        ColumnBNumbersArray(1, 1) = TempValue
    End If
'
    ReDim ResultArray(1 To UBound(ColumnBNumbersArray, 1) + 11, 1 To UBound(ColumnBNumbersArray, 1))
'
'    End If
'
    For ArrayColumn = LBound(ColumnBNumbersArray, 1) To UBound(ColumnBNumbersArray, 1)
        ColumnARow = ArrayColumn + 1
        ColumnBRow = ColumnBRow + 1
'
        For LoopCount = 1 To 10
            ColumnARow = ColumnARow + 1
            ResultArray(ColumnARow, ArrayColumn) = FormatPercent((ColumnANumbersArray(ColumnARow, 1) - ColumnBNumbersArray(ColumnBRow, 1)) / ColumnBNumbersArray(ColumnBRow, 1), 1)
        Next
    Next
'
    Range("C1").Resize(UBound(ResultArray, 1), UBound(ResultArray, 2)) = ResultArray
'
    ActiveSheet.UsedRange.EntireColumn.AutoFit
Debug.Print "Time to complete = " & Timer - StartTime & "seconds."
MsgBox "Time to complete = " & Timer - StartTime & "seconds."

End Sub

Thanks for the code but i get this error message, what could be possibly wrong?

 

[Joe4]

Sorry for not thinking it through initially and your VBA works perfectly! Thank you very much!

You are welcome.
Glad I was able to help!