Hi Yasser,
I would need a day to work through your code to understand your logic. ( If you had some
‘comments , then maybe I could understand easier. )
That is my problem: To modify your code I would need to understand it. I need a day for that.
I think me and Paul’s solution are similar. We give you a solution which puts in the formulas you want , You just need to identify the first and last rows. Our solutions put the formulas where your original test data showed them.
For this…_
YasserKhalil wrote: in rows 69 and 70 I expect to have the same result as rows 36 and 37
_... then
_......in my solution , instead of
arrTLBL() = Array(6, 35, 39, 46),
use
arrTLBL() = Array(6, 35, 39 , 68),
_.......For Paul’s solution… .._
macropod wrote:As can be seen from the formulae I posted, all you need do is identify the first and last rows..
_......
In cell B36, insert the array formula: … =SUM(IF(MOD(ROW(B$5:B$35)-CELL("Row",B$5:B$35),2)=MOD(ROW()-1,2),B$5:B$35,))
In cell B69, insert the array formula: … =SUM(IF(MOD(ROW(B$38:B$68)-CELL("Row",B$38:B$68),2)=MOD(ROW()-1,2),B$38:B$68,))
Code: Select all
Sub Paul()
Range("B36").FormulaArray = "=SUM(IF(MOD(ROW(B$5:B$35)-CELL(""Row"",B$5:B$35),2)=MOD(ROW()-1,2),B$5:B$35,))"
Range("B69").FormulaArray = "=SUM(IF(MOD(ROW(B$38:B$68)-CELL(""Row"",B$38:B$68),2)=MOD(ROW()-1,2),B$38:B$68,))"
End Sub
( I do not understand anything about those formulas, but they seem to give the correct results )
_.________________________________
If you really want your code modified, then you must help me to understand it with adding some
‘Comments. Otherwise it is a lot of work for me to understand it first.
Also I think you must try to give us better test data. Show exactly what you want. I still do not understand your explanation along with your data
My guess is that you want the output results in my worksheet
MyGuessAfter in uploaded File. But I am guessing this only because you have not explained or shown this.
_._____
To get my formals into the worksheet, “
MyGuess” or worksheet “
MyGuessAfter” I use
arrTLBL() = Array(6, 35, 39, 68, 72, 91),
The Full code to put the 3x2= 6 rows of formulas in my worksheet “
MyGuess” or worksheet “
MyGuessAfter” is
Code: Select all
Sub DoSomefinkMayBeeGuess()
Dim arrTLBL() As Variant: Let arrTLBL() = Array(6, 35, 39, 68, 72, 91)
Dim Cnt As Long
For Cnt = 0 To UBound(arrTLBL) Step 2
Dim MeFormula As String
' First Formula row
Let MeFormula = FuncyFormula(arrTLBL(Cnt + 1) - 1, arrTLBL(Cnt)): Debug.Print "MeFormula for Some first row, " & arrTLBL(Cnt + 1) + 1 & ", is " & MeFormula
Let Range("B" & arrTLBL(Cnt + 1) + 1 & ":AF" & arrTLBL(Cnt + 1) + 1 & "").Value = MeFormula
' Second Formula row
Let MeFormula = FuncyFormula(arrTLBL(Cnt + 1), arrTLBL(Cnt) + 1): Debug.Print "MeFormula for Some second row, " & arrTLBL(Cnt + 1) + 2 & ", is " & MeFormula
Let Range("B" & arrTLBL(Cnt + 1) + 2 & ":AF" & arrTLBL(Cnt + 1) + 2 & "").Value = MeFormula
Next Cnt
End Sub
Function FuncyFormula(ByVal BL As Long, ByVal TL As Long) As String
Dim Cnt As Long
For Cnt = BL To TL Step -2
Dim MeStrungOut As String
Let MeStrungOut = ",B" & Cnt & MeStrungOut
Next Cnt
Let MeStrungOut = Replace(MeStrungOut, ",", "", 1, 1, vbBinaryCompare) 'take off just 1 ","
Let FuncyFormula = "=sum(" & MeStrungOut & ")"
Debug.Print "FuncyFormula is " & FuncyFormula
End Function
The routine
Sub DoSomefinkMayBeeGuess() will make my worksheet “
MyGuess “look like my worksheet”
MyGuessAfter”
Run code
Sub DoSomefinkMayBeeGuess() with worksheet “
MyGuess “ active, and then you should get an identical worksheet to my worksheet “
MyGuessAfter”
_._____________________
I am guessing that you want help to do 2 - 3 things
_1 ) Divide your total output into sections of maximum 30 rows, with 3 new rows between sections
_2) Give the two rows of sum totals in the first two of the 3 new rows.
_3) Possibly you want some final total
Your original file and your original code and explanation in Post #1 and all your attempts in the following posts has totally confused me as to what you are trying to do
I think I know all that you want now, but I am not totally sure
Alan
File "
SalaryTBGuess.xlsm" :
https://app.box.com/s/4xo0cqcwlu4jlrj9gm1p0cmj61pujfzv" onclick="window.open(this.href);return false;
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also