Formula Errors

[Priyantha]

Dear All,

I am trying to creat a increment arrears format using exlel VBA. I tryied to diplay the below formula in my format cell. Not working correctly in my macro, but the formula is working correcly in my excel format.

[/b]My Formula with Variables :[/b]

Newsal_01 = ws.Range("D" & (startRow - 1) + i).Value '''' D11
Newsal_02 = ws.Range("D" & startRow + i).Value '''' D12
Date_Month = ws.Range("A" & startRow + i).Value '''' A12
IncreDay = ws.Range("B" & startRow + i).Value '''' B12
Oldsal_01 = ws.Range("C" & (startRow - 1) + i).Value '''' C11
Oldsal_02 = ws.Range("C" & startRow + i).Value '''' C12

ws.Range("E" & startRow + i).Formula = "=ROUND((Newsal_01/(DAY(EOMONTH(Date_Month,0)))*(IF(IncreDay>0,(IncreDay-1),IncreDay))+(Newsal_02/(DAY(EOMONTH(Date_Month,0)))*((DAY(EOMONTH(Date_Month,0)))-(IF(IncreDay>0,(IncreDay-1),IncreDay))))),2)-ROUND((Oldsal_01/(DAY(EOMONTH(Date_Month,0)))*(IF(IncreDay>0,(IncreDay-1),IncreDay))+(Oldsal_02/(DAY(EOMONTH(Date_Month,0)))*((DAY(EOMONTH(Date_Month,0)))-(IF(IncreDay>0,(IncreDay-1),IncreDay))))),2)"

Without Above Vriables:
ws.Range("E" & i).Formula = "=ROUND((D" & i - 1 & "/(DAY(EOMONTH(A" & i & ",0))*(IF(B" & i & ">0,(B" & i & "-1),B" & i & ")))+(D" & i & "/(DAY(EOMONTH(A" & i & ",0))*((DAY(EOMONTH(A" & i & ",0))-(IF(B" & i & ">0,(B" & i & "-1),B" & i & "))))),2)-ROUND((C" & i - 1 & "/(DAY(EOMONTH(A" & i & ",0))*(IF(B" & i & ">0,(B" & i & "-1),B" & i & "))+(C" & i & "/(DAY(EOMONTH(A" & i & ",0))*((DAY(EOMONTH(A" & i & ",0))-(IF(B" & i & ">0,(B" & i & "-1),B" & i & "))))),2)"

Both formulas not working. pleace help me. (My Format attached herewith)

BR,

Priyantha.

[HansV]

All the variables have to be outside the quotes:

ws.Range("E" & startRow + i).Formula = "=ROUND((" & Newsal_01 & "/(DAY(EOMONTH(" & Date_Month & ",0)))*(IF(" & IncreDay & ">0,(" & IncreDay & "-1)," & IncreDay & "))+(" & Newsal_02 & "/(DAY(EOMONTH(" & Date_Month & ",0)))*((DAY(EOMONTH(" & Date_Month & ",0)))-(IF(" & IncreDay & ">0,(" & IncreDay & "-1)," & IncreDay & "))))),2)-ROUND((" & Oldsal_01 & "/(DAY(EOMONTH(" & Date_Month & ",0)))*(IF(" & IncreDay & ">0,(" & IncreDay & "-1)," & IncreDay & "))+(" & Oldsal_02 & "/(DAY(EOMONTH(" & Date_Month & ",0)))*((DAY(EOMONTH(" & Date_Month & ",0)))-(IF(" & IncreDay & ">0,(" & IncreDay & "-1)," & IncreDay & "))))),2)"

[ChrisGreaves]

Newsal_01 = ws.Range("D" & (startRow - 1) + i).Value '''' D11
Newsal_02 = ws.Range("D" & startRow + i).Value '''' D12
Date_Month = ws.Range("A" & startRow + i).Value '''' A12
IncreDay = ws.Range("B" & startRow + i).Value '''' B12
Oldsal_01 = ws.Range("C" & (startRow - 1) + i).Value '''' C11
Oldsal_02 = ws.Range("C" & startRow + i).Value '''' C12

ws.Range("E" & startRow + i).Formula = "=ROUND((Newsal_01/(DAY(EOMONTH(Date_Month,0)))*(IF(IncreDay>0,(IncreDay-1),IncreDay))+(Newsal_02/(DAY(EOMONTH(Date_Month,0)))*((DAY(EOMONTH(Date_Month,0)))-(IF(IncreDay>0,(IncreDay-1),IncreDay))))),2)-ROUND((Oldsal_01/(DAY(EOMONTH(Date_Month,0)))*(IF(IncreDay>0,(IncreDay-1),IncreDay))+(Oldsal_02/(DAY(EOMONTH(Date_Month,0)))*((DAY(EOMONTH(Date_Month,0)))-(IF(IncreDay>0,(IncreDay-1),IncreDay))))),2)"
Without Above Vriables:
ws.Range("E" & i).Formula = "=ROUND((D" & i - 1 & "/(DAY(EOMONTH(A" & i & ",0))*(IF(B" & i & ">0,(B" & i & "-1),B" & i & ")))+(D" & i & "/(DAY(EOMONTH(A" & i & ",0))*((DAY(EOMONTH(A" & i & ",0))-(IF(B" & i & ">0,(B" & i & "-1),B" & i & "))))),2)-ROUND((C" & i - 1 & "/(DAY(EOMONTH(A" & i & ",0))*(IF(B" & i & ">0,(B" & i & "-1),B" & i & "))+(C" & i & "/(DAY(EOMONTH(A" & i & ",0))*((DAY(EOMONTH(A" & i & ",0))-(IF(B" & i & ">0,(B" & i & "-1),B" & i & "))))),2)"

Priyantha, something that you might want to consider in the future:-

Avoid deeply-nested formula.

Both forms of expression above suggest that you have placed a massive calculation into a single cell, thereby saving "space" in your worksheet.

Nowadays I can have ? 256 worksheets in a workbook and I KNOW that the number of rows can be greater than 65,000 (someone who is up-to-date will correct my Excel2003 impressions)

My brain, used as it is to reading all sorts of program code, has trouble reading anything that has more than three levels of computation, no matter if it is nested IF-statements in VBA, loops in FORTRAN, or parenthesized expressions in spreadsheet formula.
I suspect that you, like me, are human (grin!)

Try this when you have a minute to spare:-
Take the date calculations ("DAY(EOMONTH(A" & i & ",0))") and place them in a cell in a rough-work worksheet in your workbook; give the result a meaningful name, along the lines of "Day_Of_Imposition" so that the variable name makes real sense in terms of your written solution (which you are translating into Excel)
Chunks such as "IF(B" & i & ">0,(B" & i & "-1),B" & i & ")" can be placed in your rough-work sheet and identified as "Surtax_Multiplier" or similar.

You will thank yourself in six months time when you are forced to update the worksheet because of a change in taxation laws (or change of management or ...)
Cheers, Chris

[Priyantha]

Dear Hans,

It is working Correctly. Thanks,

BR,
priyantha

[Priyantha]

Dear Chris,

Thanks for your advice. I took it to heart.

BR,

Priyantha

[snb]

@chris

rows: 2^20
columns: 2^14

Code: Select all

Sub M_snb()
  ws.Range("A13") = "=DAY(EOMONTH($A$12,0)"

  ws.Range("E11") ="= ROUND((D11/$A$13)*(B12-(B12>0))+D12-B12-(B12>0)),2)-ROUND((C11/$A$13)*(B12-(B12>0))+C12-(B12-(B12>0)),2)"
End Sub

And filldown.

[Priyantha]

Thanks