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

 

[DILIPandey]

Hi Jeremy,

Can you post a small sample along with your expected output- manually entered ?



Regards,
DILIPandey

 

[ask2tsp]

Is something like this what you want1?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	inventory	week
2	item	1	3	5	7	9	11	13	15	17	19	21	23	25	27	29	31	33	35	37	39	41
3	item 1	90	81	71	67	60	58	46	43	24	22	13	8	76	61	56	43	38	36	35	29
4	item 2	168	162	157	147	142	135	134	124	120	103	91	76	57	50	39	37	24	16	92	90
5	item 3	195	183	176	173	163	159	139	136	135	128	117	99	88	80	63	46	33	19	99	92
6	item 4	109	101	87	83	75	64	48	37	28	18	13	61	51	37	19	12	6	30	27	16
7	item 5	55	43	36	18	58	54	40	25	13	77	74	58	54	35	34	33	17	9	45	43
8	item 6	33	30	29	15	59	48	44	35	20	80	72	54	39	37	24	80	80	71	63	57
9	item 7	207	191	177	169	154	148	141	132	113	111	98	91	89	85	83	74	55	44	34	110
10
Sheet1

<p style="width:4,2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">records</p><br /><br /><table width="35%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=B2+2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=C2+2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=D2+2</td></tr></tbody></table></td></tr></table><br />

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	delta	week
2	item	1	3	5	7	9	11	13	15	17	19	21	23	25	27	29	31	33	35	37
3	item 1		-9	-10	-4	-7	-2	-12	-3	-19	-2	-9	-5	68	-15	-5	-13	-5	-2	-1
4	item 2		-6	-5	-10	-5	-7	-1	-10	-4	-17	-12	-15	-19	-7	-11	-2	-13	-8	76
5	item 3		-12	-7	-3	-10	-4	-20	-3	-1	-7	-11	-18	-11	-8	-17	-17	-13	-14	80
6	item 4		-8	-14	-4	-8	-11	-16	-11	-9	-10	-5	48	-10	-14	-18	-7	-6	24	-3
7	item 5		-12	-7	-18	40	-4	-14	-15	-12	64	-3	-16	-4	-19	-1	-1	-16	-8	36
8	item 6		-3	-1	-14	44	-11	-4	-9	-15	60	-8	-18	-15	-2	-13	56	0	-9	-8
9	item 7		-16	-14	-8	-15	-6	-7	-9	-19	-2	-13	-7	-2	-4	-2	-9	-19	-11	-10
10
Sheet1

<p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">trends</p><br /><br /><table width="35%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=records!C3-records!B3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D3</th><td style="text-align:left">=records!D3-records!C3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=records!C4-records!B4</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D4</th><td style="text-align:left">=records!D4-records!C4</td></tr></tbody></table></td></tr></table><br />

 

[JeremyGraham95]

I will be scanning barcodes for the items and the quantity of each. i have code and formulas that will fill in the entire workbook with the correct information. I have the information being automatically put into the "RECORDS" sheet and all i am needing is a piece of code that takes the last 2 columns with data and subtracts them from each other and paste that into the "TRENDS" sheet in the first empty column.

 

[ask2tsp]

If you copy the formula in trends sheet in all the columns, then you don't have to do that copy/paste.
I changed the formula so it handles empty values better

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	delta	week
2	item	1	3	5	7	9	11	13	15	17	19	21	23	25	27	29	31	33	35	37	39	41	43	45	47	49	51
3	item 1		-9	-10	-4	-7	-2	-12	-3	-19	-2	-9	-5	68	-15	-5	-13	-5	-2	-1	-6
4	item 2		-6	-5	-10	-5	-7	-1	-10	-4	-17	-12	-15	-19	-7	-11	-2	-13	-8	76	-2
5	item 3		-12	-7	-3	-10	-4	-20	-3	-1	-7	-11	-18	-11	-8	-17	-17	-13	-14	80	-7
6	item 4		-8	-14	-4	-8	-11	-16	-11	-9	-10	-5	48	-10	-14	-18	-7	-6	24	-3	-11
7	item 5		-12	-7	-18	40	-4	-14	-15	-12	64	-3	-16	-4	-19	-1	-1	-16	-8	36	-2
8	item 6		-3	-1	-14	44	-11	-4	-9	-15	60	-8	-18	-15	-2	-13	56		-9	-8	-6
9	item 7		-16	-14	-8	-15	-6	-7	-9	-19	-2	-13	-7	-2	-4	-2	-9	-19	-11	-10	76
10
11
12
trends

Cell Formulas
Range	Formula
C3	=IF(records!C3=0,0,records!C3-records!B3)
C4	=IF(records!C4=0,0,records!C4-records!B4)
D3	=IF(records!D3=0,0,records!D3-records!C3)
D4	=IF(records!D4=0,0,records!D4-records!C4)

 

[JeremyGraham95]

I understand the fromula you wrote but what i want is for it to be VBA. because i am planning to use it on the click of a button.

 

[tonyyy]

You might give this a try...

Sub Trends()

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

LastCol1 = ThisWorkbook.Sheets("Records").Cells(3, Columns.Count).End(xlToLeft).Column
LastCol2 = ThisWorkbook.Sheets("Trends").Cells(3, Columns.Count).End(xlToLeft).Column + 1
ReDim arr1(1 To 128, 1 To 1)
ReDim arr2(1 To 128, 1 To 1)
ReDim arr3(1 To 128, 1 To 1)

arr1 = ThisWorkbook.Sheets("Records").Range(Sheets("Records").Cells(3, LastCol1), Sheets("Records").Cells(128, LastCol1)).Value
arr2 = ThisWorkbook.Sheets("Records").Range(Sheets("Records").Cells(3, LastCol1 - 1), Sheets("Records").Cells(128, LastCol1 - 1)).Value

For i = LBound(arr1) To UBound(arr1)
    arr3(i, 1) = arr2(i, 1) - arr1(i, 1)
Next i

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

End Sub

Based on your initial post, it's assumed your records start in Row 3 and end at Row 128.

Cheers,

tonyyy

 

[ask2tsp]

Jeremy sorry for the delay. I was sick for a couple of days (nearly a pneumonia)


I wrote a macro that does the job for me. As you could see in an ealier respons I use 2 header rows in my sheet. When your number of header rows are different please let me know and I'll adjust that in the macro (easy change).

In my test sheet I used weeknumbers, because you said "i do this every other week". If you want to maintain dates in your records sheet, I do not use that line of info so by all means leave it like that. If you want dates in the header of the trends sheet, I can do that too, but it is a bit more work (couple of hours).

Take a peek at the code. I am very interested if you got some idea how this code works. I added some comments and by using meaningfull variable names ( the things after the Dim keywords) my goal 'a macro must read like a book' is almost achirved.

Now then, what you have been waiting for... The code

Option Explicit

Sub appendNewRate()
'calculate (last column - beforelast column) of records sheet
'and put it after the last column of the trends sheet having data
   
   '----------------------------------------------------------------------
   ' If your sheet(s) have other label(s) (case does not matter),
   ' change the name between "" in (one of) the following two Const lines
   
   Const RECORD_NAME As String = "records"
   Const TREND_NAME  As String = "trends"
   '----------------------------------------------------------------------

   Dim shtRecord           As Worksheet
   Dim shtRecordRow        As Long
   Dim shtRecordLastRow    As Long
   Dim shtRecordLastColumn As Long
   
   Dim shtTrend            As Worksheet
   Dim shtTrendRow         As Long
   Dim shtTrendLastColumn  As Long
   
   Dim LastTwoDifference   As Integer
   '-------------------------------- end of declarations -----------------
   
   Set shtRecord = ThisWorkbook.Worksheets(RECORD_NAME)
   Set shtTrend = ThisWorkbook.Worksheets(TREND_NAME)
   
   shtRecordLastRow = shtRecord.Range("A3").End(xlDown).Row
   shtRecordLastColumn = shtRecord.Range("A3").End(xlToRight).Column
   shtTrendLastColumn = shtTrend.Range("C3").End(xlToRight).Column
   
   If shtTrendLastColumn < 4 Then
      MsgBox ("You need at least two columns of data" & vbLf & _
              "to perform this operation")
      Exit Sub
   End If
   
   For shtRecordRow = 3 To shtRecordLastRow
      
      LastTwoDifference = shtRecord.Cells(shtRecordRow, shtRecordLastColumn).Value _
                    - shtRecord.Cells(shtRecordRow, shtRecordLastColumn - 1).Value
   
      shtTrend.Cells(shtRecordRow, shtTrendLastColumn + 1).Value = LastTwoDifference
   Next shtRecordRow
End Sub

 

[ask2tsp]

@tonyyy: your code is, no doubt, faster than my solution, but it is hard to read. And frankly, only 128 rows, who cares about speed?

 

[tonyyy]

@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
LastCol2 = ThisWorkbook.Sheets("Trends").Cells(3, Columns.Count).End(xlToLeft).Column + 1
ReDim arr1(1 To 128, 1 To 2)
ReDim arr2(1 To 128, 1 To 1)

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