Populate active cell value with neighboring cell value

[mgareveys]

Hi All,

Thanks for having me on this forum, and pardon my grammar too. I have daily sale spreadsheet that I would like to auto populate cells with user defined vba functions that takes in the daily egg laying from a user and calculates the number of creates and then calculate the daily sales. This is for the month, thus from 1st to 31st depends which month we're dealing with. The problem I have is getting the crates for the day is easy (.e.i. crates = Quotient(eggs,30)), but this will have a remainder of (Eggs = egg - 30*Quotient(eggs,30)) and where (Sale = crates*rate). I would like to be able to add whatever that got remained from yesterday to todays numbers and repeated the process again till the end of the month. The other thing is the beginning of the month starts a new row which I cannot or perhaps not sure how I can access. I have written some functions which doesn't seem to be working. I will gladly appreciate your help on this, also please find attached image for your reference that may be helpful.

Public Function leftValue() As Integer
    
    leftValue = Application.Caller.Offset(-1, -1).Value
End Function

Function getRemainder(eggs As Integer)
    
    crates = Application.WorksheetFunction.Quotient(eggs, 30)
    
    getRemainder = eggs - crates * 30
    
End Function


Function SumPrice(eggs As Integer, rate As Double)
    Application.Volatile True
 
    getRemain = eggs + leftValue()
    'yesEggs = getRemainder(eggs)
    crates = Application.WorksheetFunction.Quotient(getRemain, 30)
    SumPrice = crates * rates
End Function

 

[RobP]

Hi not easy to read your picture, as not very large. Better to post actual excel sheet (say the first three months rows only) using the XL2BB button above.

Sorry I'm not an expert at the UDF's you show - but looks to me your "leftValue()" is failing for some reason.
Nothing wrong with your getRemainder() function that I can see.

I can't tell what you are doing with the rest of your code as you don't share - but to me this kind of task looks well suited to the new Lambda() function on O365.
For example you can define =LAMBDA(eggs, QUOTIENT(eggs,30)) as "Crates" in your name manager (Ctrl+F3).
For your remainders, I would use a formula just like =MOD(P8,30) inside I perhaps cell P10 (if your "eggs" row is in fact for your remaining eggs of that day or MOD(P8+O10,30) if you wanted to add the day before ?

Does it help at all ?
Rob

 

[mgareveys]

Hi Rob,

Thanks for the suggestion and for the quick response. I actually started learning VBA during the weekend, I thought my query was simple and I wanted to try it out to make my data entry more automated. And yes you're right the leftValue() function doesn't seem to work when I call it in another vba function however, when I call it in an active cell it works. I'm a little perplex at the beginning of every month since the remainder from the previous day is at the far right of the sheet with couple of rows and columns. I'll try your recommendation and see how it works out. Also, I have the following worksheet for three months perhaps that'll be more clearer:

Personal.xlsb
	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	AB	AC	AD	AE	AF	AG	AH
44	Aug.	Egg Count	63	74	75	91	114	132	138	152	171	177	191	190	230	235	225	225	240	241	258	261	265	267	274	279	290	291	283	304				5736
45		Crates	2	2	2	3	3	4	4	5	5	5	6	6	7	7	7	7	8	8	8	8	8	8	9	9	9	9	9	10	0	0	0	191
46		Eggs	3	14	15	1	24	12	18	2	21	27	11	10	20	25	15	15	0	1	18	21	25	27	4	9	20	21	13	4	0	0	0	396
47		Sales	56	56	56	84	84	112	112	140	140	140	168	168	196	196	196	196	224	224	224	224	21	0	0	0	0	0	0	0	0	0	0	3017
48		Expens.																																0
49		Profit/Loss	56	56	56	84	84	112	112	140	140	140	168	168	196	196	196	196	224	224	224	224	21	0	0	0	0	0	0	0	0	0	0	3017
50	Sep.	Egg Count																																0
51		Crates	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
52		Eggs	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
53		Sales	#VALUE!	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	#VALUE!
54		Expens.																																0
55		Profit/Loss	#VALUE!	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	#VALUE!
56	Oct.	Egg Count																																0
57		Crates	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
58		Eggs
59		Sales	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
60		Expens.																																0
61		Profit/Loss	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2022

Cell Formulas
Range	Formula
C57:AH57,C45:AH45,C51:AH51	C45	=QUOTIENT(C44,30)
C46:AG46,C52:AG52	C46	=getRemainder(C44)
C47:V47	C47	=28*C45
W47	W47	=leftValue()
X47,D53:E53	X47	=sumprice(W46+X44,rate)
Y47:AG47	Y47	=sumprice(Y44+Y46,rate)
C61:AH61,C49:AH49,C55:AH55	C49	=C47-C48
C53,F53:AG53	C53	=sumprice(C50,rate)
AH44,AH46:AH48,AH50,AH52:AH54,AH56,AH59:AH60	AH44	=SUM(C44:AG44)
C59:AG59	C59	=rate*C57

Named Ranges
Name	Refers To	Cells
rate	='2022'!$AJ$2	X47:AG47, C53:AG53, C59:AG59

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C61:AH61	Cell Value	<0	text	NO
C61	Other Type	Color scale		NO
C55:AH55	Cell Value	<0	text	NO
C55	Other Type	Color scale		NO
C49:AH49	Cell Value	<0	text	NO
C49	Other Type	Color scale		NO

 

[RobP]

Hi, so by using formula instead of VBA, you can "carry over" your remaining eggs across the sheet to column AG (regardless if the month has 28,30 or 31 days in it). This gives you a reference to grab it for your next month.
I defined my Lambda function as I mentioned previous, and renamed it "Crates". Cell C45 is using todays egg count + yesterdays remainder to work out how many crates. It references the end of the previous month cell eggs in AG40. (=Crates(C44+AG40))

Cell C46 has "=MOD(C44+AG40,30)" again using yesterdays remainder + todays egg count.

Then I copied across the row the formula D45->AG45 =Crates(D44+C46)
and again the formula =MOD(D44+C46,30) in D46->AG46

So important, the formulas in Col C are different to D->AG, as they are referring to last months cell location, rather than yesterdays.

Hope that helps you.

Cheers
Rob

 

[mgareveys]

AWESOME rob!!!!! This works I really didn't need vba for this at all... thanks for pointing it out!

 

[RobP]

great - glad to have been useful. Many thanks for the feedback.