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]

try this code:

Sub test()
' define double quotes character
tt = Chr(34)
'=IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10))))
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' load colummn D and F into variant arrays

inarr = Range(Cells(1, 4), Cells(lastrow, 4))
outarr = Range(Cells(1, 6), Cells(lastrow, 6))
sumrow = 2

For i = 2 To lastrow

If inarr(i, 1) = "Summary" Then
  ' reset count
  sumrow = i
End If
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 & ")))))"
Next i
Range(Cells(1, 6), Cells(lastrow, 6)) = outarr

End Sub

 

[Peter Muller]

try this code:

Sub test()
' define double quotes character
tt = Chr(34)
'=IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10))))
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' load colummn D and F into variant arrays

inarr = Range(Cells(1, 4), Cells(lastrow, 4))
outarr = Range(Cells(1, 6), Cells(lastrow, 6))
sumrow = 2

For i = 2 To lastrow

If inarr(i, 1) = "Summary" Then
  ' reset count
  sumrow = i
End If
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 & ")))))"
Next i
Range(Cells(1, 6), Cells(lastrow, 6)) = outarr

End Sub

Thank you for your assist - i am completely lost, cannot get it to work, been trying for hours.

i may be defining my variables incorrectly, also getting this error message

 

[offthelip]

Try it without declaring any variables and without option explicit, and then put a break point in and look at the locals window to see what the variables actually are, then you can declare like that if you want to.

 

[Peter Muller]

Try it without declaring any variables and without option explicit, and then put a break point in and look at the locals window to see what the variables actually are, then you can declare like that if you want to.

Nope, still not working for me.

the other code was written for me, and after trying to make sense of it, I am still baffled, so trying to integrate your code is just not working, despite following your suggestions.

Any chance you can have a look at integrating your code with the rest of the code which performs all of the other transactions or am i asking/expecting too much?

 

[offthelip]

I don't know what your other code is but there must be some misunderstanding that i have made, but try, :
open a blank workbook
put any character e.g T into cell A1 and copy down to A20, put "Summary" into D2 and into D9 , then run the code that I posted it will write the equations that you asked for into column F
I have annotated my code with more comments:

Sub test()
' define double quotes character
tt = Chr(34)
'=IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10))))
' this works out what the last row is by looking at column A, this could be the problem chage it to another column
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' load colummn D and F into variant arrays
' these tow lines load column D and column F into varaint arrays
inarr = Range(Cells(1, 4), Cells(lastrow, 4))
outarr = Range(Cells(1, 6), Cells(lastrow, 6))
' this initialises the start of the sum row
sumrow = 2
' this loops through all the rows as determined by checking for values in column A
For i = 2 To lastrow
' this checks if column D has the workd Summary on this row
If inarr(i, 1) = "Summary" Then
  'if so we reset the sum start row reset count
  sumrow = i
End If
' this writes the required equatiosn into the output array. remember tt is a double quote constant
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 & ")))))"
Next i
' after the loop we writethe variatn array back to column F
Range(Cells(1, 6), Cells(lastrow, 6)) = outarr

End Sub

 

[Peter Muller]

I don't know what your other code is but there must be some misunderstanding that i have made, but try, :
open a blank workbook
put any character e.g T into cell A1 and copy down to A20, put "Summary" into D2 and into D9 , then run the code that I posted it will write the equations that you asked for into column F
I have annotated my code with more comments:

Sub test()
' define double quotes character
tt = Chr(34)
'=IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10))))
' this works out what the last row is by looking at column A, this could be the problem chage it to another column
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' load colummn D and F into variant arrays
' these tow lines load column D and column F into varaint arrays
inarr = Range(Cells(1, 4), Cells(lastrow, 4))
outarr = Range(Cells(1, 6), Cells(lastrow, 6))
' this initialises the start of the sum row
sumrow = 2
' this loops through all the rows as determined by checking for values in column A
For i = 2 To lastrow
' this checks if column D has the workd Summary on this row
If inarr(i, 1) = "Summary" Then
  'if so we reset the sum start row reset count
  sumrow = i
End If
' this writes the required equatiosn into the output array. remember tt is a double quote constant
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 & ")))))"
Next i
' after the loop we writethe variatn array back to column F
Range(Cells(1, 6), Cells(lastrow, 6)) = outarr

End Sub

Great, this is working.

thank you so much, I appreciate your patience and assistance.

?

 

[offthelip]

I am please we got there in the end!!

 

[Peter Muller]

I am please we got there in the end!!

Yes, so am I

I have two questions based on your code, but let me try to resolve it myself, otherwise i will get back to you in the morning.

 

[Peter Muller]

I don't know what your other code is but there must be some misunderstanding that i have made, but try, :
open a blank workbook
put any character e.g T into cell A1 and copy down to A20, put "Summary" into D2 and into D9 , then run the code that I posted it will write the equations that you asked for into column F
I have annotated my code with more comments:

Sub test()
' define double quotes character
tt = Chr(34)
'=IF($G10="Yes",H10,IF(G10="No",0,IF(G10="Sum",SUM(F$10:F10)/SUM(H$10:H10))))
' this works out what the last row is by looking at column A, this could be the problem chage it to another column
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
' load colummn D and F into variant arrays
' these tow lines load column D and column F into varaint arrays
inarr = Range(Cells(1, 4), Cells(lastrow, 4))
outarr = Range(Cells(1, 6), Cells(lastrow, 6))
' this initialises the start of the sum row
sumrow = 2
' this loops through all the rows as determined by checking for values in column A
For i = 2 To lastrow
' this checks if column D has the workd Summary on this row
If inarr(i, 1) = "Summary" Then
  'if so we reset the sum start row reset count
  sumrow = i
End If
' this writes the required equatiosn into the output array. remember tt is a double quote constant
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 & ")))))"
Next i
' after the loop we writethe variatn array back to column F
Range(Cells(1, 6), Cells(lastrow, 6)) = outarr

End Sub

Good morning,

Is it possible to modify your code that the calculation in col D does not calculate from F$2 until the last row, but only for the relevant section for the day? in this case it would be F$1:F2 because “Summary” is in D2, then the next calc must be between F$3:F9 because “Summary” is in D9, and so on.

The calculation if Col F must therefore not run from F2 every time, but only for the rows added for the day, which is between 1 row down from “Summary” in col D till the next row with “Summary” in col D.