Enter formula from one sheet into different sheet

[JeremyGraham95]

I am working on an inventory spreadsheet and have multiple things that i have working. but the one thing i seem to be struggling with is that i have a sheet "RECORDS" which keeps track of how many of each item i have for the date the inventory is checked which is every other week). what i am trying to do is take the last 2 columns in the RECORDS sheet and subtract them from each other and paste that value in my new sheet "TRENDS" on the first empty column in this sheet. so i want a3-b3 to be put in the new sheet in the third row and so on all the way down to the 128th row. could anyone please help me. It would be greatly appreciated and i am needing this done by 01/25/2016. Thanks to anyone and everyone that attempts t help me out!!!


*****I already have a sheet called "TRENDS" and i want to post the results of that formula in the first empty column in the TRENDS sheet. the sheet already exists and i want this code to just keep track of how much of an item is being used

 

[ask2tsp]

@ask2tsp,

Thanks for the critique. Please let me know which part(s) of the code is hard to read and I'll do my best to explain.

As for speed, my aim is to write code that will run most efficiently, and for me, that's a constant learning process. Even as I review my initial code, I realize I can reduce the number of arrays to one, by combining arr1 and arr2 into a two dimensional array.

Sub Trends2()

Dim LastCol1 As Long
Dim LastCol2 As Long
Dim arr1() As Variant
Dim arr2() As Variant
Dim i As Long

LastCol1 = ThisWorkbook.Sheets("Records").Cells(3, Columns.Count).End(xlToLeft).Column  '<<---ThisWorkbook.Sheets("Trends") to worksheet var, then replace everywhere for that varname
 
lastcol2=ThisWorkbook.Sheets("Trends").Columns.Count).End(xlToLeft).Column + 1
ReDim arr1(1 To 128, 1 To 2)
ReDim arr2(1 To 128, 1 To 1) '<<--- this is, in fact, one-dimensional

arr1 = ThisWorkbook.Sheets("Records").Range(Sheets("Records").Cells(3, LastCol1 - 1), Sheets("Records").Cells(128, LastCol1)).Value
For i = LBound(arr1) To UBound(arr1)
    arr2(i, 1) = arr1(i, 1) - arr1(i, 2)
Next i

ThisWorkbook.Sheets("Trends").Range(Sheets("Trends").Cells(3, LastCol2), Sheets("Trends").Cells(128, LastCol2)).Value = arr2

End Sub

Hope you're over your pneumonia.

tonyyy

yes, the pneumonia is not quite there yet but things are improving.

I inserted a few comments prefixed with <<---
Maybe they are usefull
btw. since vba performance is your thing: (not sure) putting the sheet into an worksheet object and using this var saves two 'select objec by name ' executions