VBA Assistance - Conditional calculation

[Peter Muller]

Hello Experts,

I desperately need help to finish a task I am busy with, which stems from my previous post.

I require VBA coding for the following:

Column’s B-E is populated daily, below the last entry the previous day. The # of entries for the day will vary, dependent on certain criteria. The last entry for the day will always be “Summary”, which is pasted into Column D.

In my example below, Row 9 is the sum row for the previous day, and rows 10-14 are the pasted rows for the new day.

The formula in Col F should thus read:

Previous Day F9) =IF($G9="Yes",H9,IF(G9="No",0,IF(G9="Sum",SUM(F$6:F9)/SUM(H$6:H9)))) New Day

F10) =IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10))))

F11) =IF($G11="Yes",H11,IF(G11="No",0,IF(G11="Sum",SUM(F$10:F11)/SUM(H$10:H11))))

F12) =IF($G12="Yes",H12,IF(G12="No",0,IF(G12="Sum",SUM(F$10:F12)/SUM(H$10:H12))))

F13) =IF($G13="Yes",H13,IF(G13="No",0,IF(G13="Sum",SUM(F$10:F13)/SUM(H$10:H13))))

F14) =IF($G14="Yes",H14,IF(G14="No",0,IF(G14="Sum",SUM(F$10:F14)/SUM(H$10:H14))))

		B	C	D	E	F	G	H
		Project	Category	Description	Date	score	Completed	Weight	Details/Formula in Col F
9		PM	Score	Summary	Fri 22/05	76%			=IF($G9="Yes",H9,IF(G9="No",0,IF(G9="Sum",SUM(F$6:F9)/SUM(H$6:H9))))
10		Finance	Petty Cash	Reconcilliation	Mon 25/05	5	Yes	5	=IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10))))
11		Finance	Petty Cash	Replenish cash	Mon 25/05	5	Yes	5	=IF($G11="Yes",H11,IF(G11="No",0,IF(G11="Sum",SUM(F$10:F11)/SUM(H$10:H11))))
12		HR	T&A	Registers monitored	Mon 25/05	0	No	2	=IF($G12="Yes",H12,IF(G12="No",0,IF(G12="Sum",SUM(F$10:F12)/SUM(H$10:H12))))
13		HR	T&A	Late & Absent actioned	Mon 25/05	FALSE		2	=IF($G13="Yes",H13,IF(G13="No",0,IF(G13="Sum",SUM(F$10:F13)/SUM(H$10:H13))))
14		PM	Score	Summary	Mon 25/05	0	Sum		=IF($G14="Yes",H14,IF(G14="No",0,IF(G14="Sum",SUM(F$10:F14)/SUM(H$10:H14))))

 

[offthelip]

I think I understand, :try changing this line;

If inarr(i, 1) = "Summary" Then

to

If inarr(i - 1, 1) = "Summary" Then

 

[Peter Muller]

I think I understand, :try changing this line;

If inarr(i, 1) = "Summary" Then

to

If inarr(i - 1, 1) = "Summary" Then

Thanks, but it is still calculating from the 1st row, so in my case above, the calc includes F1:F2, instead of starting at F3

 

[offthelip]

I don't understand what you want. Is the problem just where it starts on the worksheet. i.e Are the equations from row 3 onwards correct. If this is the case can you write out what equations you want in F1 to F3 and tell what is in column D. If the probelm is all the way down the work sheet you are going to need to explain it a bit more carefully may be with more examples

 

[Peter Muller]

I don't understand what you want. Is the problem just where it starts on the worksheet. i.e Are the equations from row 3 onwards correct. If this is the case can you write out what equations you want in F1 to F3 and tell what is in column D. If the probelm is all the way down the work sheet you are going to need to explain it a bit more carefully may be with more examples

I add in a random amount of rows on a daily basis, so day 1 may be from Rows 1 to Row 5, where Row 5 is the “Summary” row, and therefore the last entry for the day. For this day, the sum calculation will be from rows 1 – 5.

The following day I add another amount of rows, which may be from rows 6 to 12, where row 12 is the “Summary” row, and therefore the last entry for the day. For this day, the calculation must be from rows 6 to 12, and not from rows 1 to12 as it presently is.

The formula, as is, calculates everything from Rows 1 to 12, therefore rendering the answer for the second day, incorrect.

If you can change the formula not to start at row 1, but let it start one row below the previous day’s total, where “Summary” was the last row, then it will work.

Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H		Explanation of Col F
	Project	Category	Description	Date	Score	Completed	Weight		Col F formula in detail
Row 3	Finance	Petty Cash	Reconcilliation	Tue 12/05	5	Yes	5		=IF($G3="Yes",H3,IF(G3="No",0,IF(G3="Sum",SUM(F2:F$3)/SUM((H2:H$3)))))	The calc range is from rows 3 - 5
Row 4	Finance	Creditors	Supplier Recons - A-F	Tue 12/05	3	Yes	3		=IF($G4="Yes",H4,IF(G4="No",0,IF(G4="Sum",SUM(F$3:F3)/SUM((H$3:H3)))))
	PM	Score	Summary	Tue 12/05	1	Sum			'=IF($G5="Yes",H5,IF(G5="No",0,IF(G5="Sum",SUM(F$3:F4)/SUM((H$3:H4)))))
	Finance	Petty Cash	Reconcilliation	Wed 13/05	5	Yes	5		=IF($G6="Yes",H6,IF(G6="No",0,IF(G6="Sum",SUM(F5:F$6)/SUM((H5:H$6)))))	New day, calc range changes from rows 3 - 5 to rows 6-12
	Finance	Creditors	Supplier Recons - A-F	Wed 13/05	3	Yes	3		=IF($G7="Yes",H7,IF(G7="No",0,IF(G7="Sum",SUM(F$6:F6)/SUM((H$6:H6)))))
	Finance	Creditors	Bank Recons	Wed 13/05	0	No	5		=IF($G8="Yes",H8,IF(G8="No",0,IF(G8="Sum",SUM(F$6:F7)/SUM((H$6:H7)))))
	HR	T&D	Updated Skills Matrix	Wed 13/05	0	No	2		=IF($G9="Yes",H9,IF(G9="No",0,IF(G9="Sum",SUM(F$6:F8)/SUM((H$6:H8)))))
	HR	T&A	Registers monitored	Wed 13/05	4	Yes	4		=IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$6:F9)/SUM((H$6:H9)))))
	HR	T&A	Late & Absent actioned	Wed 13/05	4	Yes	4		=IF($G11="Yes",H11,IF(G11="No",0,IF(G11="Sum",SUM(F$6:F10)/SUM((H$6:H10)))))
	PM	Score	Summary	Wed 13/05	1	Sum	FALSE		'=IF($G12="Yes",H12,IF(G12="No",0,IF(G12="Sum",SUM(F$6:F11)/SUM((H$6:H11)))))

 

[offthelip]

I don't know what you have done but the equations which my code puts into column F do not agree with what you have got Get these equations starting in Row 2, I have got Summary in row 5 just as your data has:
=IF($G2="Yes",H2,IF(G2="No",0,IF(G2=" Sum",SUM(F$2:F2)/SUM((H$2:H2)))))
=IF($G3="Yes",H3,IF(G3="No",0,IF(G3=" Sum",SUM(F$2:F3)/SUM((H$2:H3)))))
=IF($G4="Yes",H4,IF(G4="No",0,IF(G4=" Sum",SUM(F$2:F4)/SUM((H$2:H4)))))
=IF($G5="Yes",H5,IF(G5="No",0,IF(G5=" Sum",SUM(F$2:F5)/SUM((H$2:H5)))))
=IF($G6="Yes",H6,IF(G6="No",0,IF(G6=" Sum",SUM(F$6:F6)/SUM((H$6:H6)))))

Is this what you want?
You cause confusion because as far as I can see NONE of the equations start or reference row 1, so your words don't agree with with the equations you post. The explanations of the equations which you have given are inconsistent, look at rows , 6 ,7 , 8 and 9.( the rows after the summary) look at the last sum it goes from H5:H6 , H6:H6, H6:H7, H6:H8. Impossible to program with the information you have given.
My equations ( the latest version) Gives
H2:h5
H6:H6
H6:h7
H6:h8
which I believe is what you asked for.
Please can you clear up what you requirements really are!!!
,

 

[Peter Muller]

I don't know what you have done but the equations which my code puts into column F do not agree with what you have got Get these equations starting in Row 2, I have got Summary in row 5 just as your data has:
=IF($G2="Yes",H2,IF(G2="No",0,IF(G2=" Sum",SUM(F$2:F2)/SUM((H$2:H2)))))
=IF($G3="Yes",H3,IF(G3="No",0,IF(G3=" Sum",SUM(F$2:F3)/SUM((H$2:H3)))))
=IF($G4="Yes",H4,IF(G4="No",0,IF(G4=" Sum",SUM(F$2:F4)/SUM((H$2:H4)))))
=IF($G5="Yes",H5,IF(G5="No",0,IF(G5=" Sum",SUM(F$2:F5)/SUM((H$2:H5)))))
=IF($G6="Yes",H6,IF(G6="No",0,IF(G6=" Sum",SUM(F$6:F6)/SUM((H$6:H6)))))

Is this what you want?
You cause confusion because as far as I can see NONE of the equations start or reference row 1, so your words don't agree with with the equations you post. The explanations of the equations which you have given are inconsistent, look at rows , 6 ,7 , 8 and 9.( the rows after the summary) look at the last sum it goes from H5:H6 , H6:H6, H6:H7, H6:H8. Impossible to program with the information you have given.
My equations ( the latest version) Gives
H2:h5
H6:H6
H6:h7
H6:h8
which I believe is what you asked for.
Please can you clear up what you requirements really are!!!
,

I am so sorry for the miscommunication.

I made all the adjustments to your code which you gave to me yesterday to start at Row 3, etc., so all of that works well, your code was exactly right, thank you.

My only requirement is that only the relevant day, which is being copied in, must be ‘calculated’ and not the whole range.

I need the code to identify that it starts calculating for the new day (and therefore the new range) where the previous day was “Summary”.

So in my last example, when I copy in the new day, Rows 6 to 12, the formula in F changes to start from F6 instead of from F3

 

[offthelip]

Still not clear, What do you want to happen to the equations in Rows 3 to 5

 

[offthelip]

another thing the code doesn't Calculate anything it just write equations in, so your comments just don't fit what iust happening

 

[Peter Muller]

another thing the code doesn't Calculate anything it just write equations in, so your comments just don't fit what iust happening

Once again, thanks for your patience, and willingness to continue helping me.

I will get back to you in the morning, and will hopefully by then be able to express my needs a lot clearer.

I am hopeful that you will still have time to assist me then.

Is there away that i can send you my Excel file?

 

[offthelip]

the reason the code might not be doing what you expect is possibly because there was an extra space before the "Sum" check
change this line;

outarr(i, 1) = "=IF($G" & i & "=" & tt & "Yes" & tt & ",H" & i & ",IF(G" & i & "=" & tt & "No" & tt & " ,0,IF(G" & i & "=" & tt & " Sum" & tt & ",SUM(F$" & sumrow & ":F" & i & ")/SUM((H$" & sumrow & ":H" & i & ")))))"

to

outarr(i, 1) = "=IF($G" & i & "=" & tt & "Yes" & tt & ",H" & i & ",IF(G" & i & "=" & tt & "No" & tt & " ,0,IF(G" & i & "=" & tt & "Sum" & tt & ",SUM(F$" & sumrow & ":F" & i & ")/SUM((H$" & sumrow & ":H" & i & ")))))"

Note if that doesn't fix your problem then I have written the requirements as I understand them, please modify them to meet what you want:
Requirements;

Produce VBA code that will write equations into Column F of a worksheet starting in Row 2 and continuing to down to the last row with data in it as determined by looking at data in ColumnA

The equation written into the column F should be an if statement which can give one of three values

Which of the three values is determined by looking at value in column G of the same row.

If G has a value of “Yes” then the equation picks the value from column H of the same row

If G has a value of “No” then the equation picks the value of zero

If G has a value of “Sum” then the equation does a calculation which is the sum of the values in column F from one row after the last occurrence above of “Summary” in column to the current row, divided by the sum of the values in column H from one row after the last occurrence above of “Summary” in column to the current row