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))))

 

[Peter Muller]

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

Thank you kindly,

You have certainly helped me, and I am most thankful.