Formula Errors

Priyantha
StarLounger
Posts: 86
Joined: 10 Oct 2022, 02:52

Formula Errors

Post by 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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78421
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula Errors

Post by 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)"
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15587
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Formula Errors

Post by ChrisGreaves »

Priyantha wrote:
16 Feb 2024, 11:48

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
There's nothing heavier than an empty water bottle

Priyantha
StarLounger
Posts: 86
Joined: 10 Oct 2022, 02:52

Re: Formula Errors

Post by Priyantha »

Dear Hans,

It is working Correctly. Thanks,

BR,
priyantha

Priyantha
StarLounger
Posts: 86
Joined: 10 Oct 2022, 02:52

Re: Formula Errors

Post by Priyantha »

Dear Chris,

Thanks for your advice. I took it to heart.

BR,

Priyantha

snb
4StarLounger
Posts: 574
Joined: 14 Nov 2012, 16:06

Re: Formula Errors

Post by 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
StarLounger
Posts: 86
Joined: 10 Oct 2022, 02:52

Re: Formula Errors

Post by Priyantha »

Thanks