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.
Formula Errors
-
- StarLounger
- Posts: 92
- Joined: 10 Oct 2022, 02:52
Formula Errors
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78532
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula Errors
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)"
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
Hans
-
- PlutoniumLounger
- Posts: 15640
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Formula Errors
Priyantha, something that you might want to consider in the future:-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)"
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
He who plants a seed, plants life.
-
- StarLounger
- Posts: 92
- Joined: 10 Oct 2022, 02:52
Re: Formula Errors
Dear Hans,
It is working Correctly. Thanks,
BR,
priyantha
It is working Correctly. Thanks,
BR,
priyantha
-
- StarLounger
- Posts: 92
- Joined: 10 Oct 2022, 02:52
Re: Formula Errors
Dear Chris,
Thanks for your advice. I took it to heart.
BR,
Priyantha
Thanks for your advice. I took it to heart.
BR,
Priyantha
-
- 4StarLounger
- Posts: 584
- Joined: 14 Nov 2012, 16:06
Re: Formula Errors
@chris
rows: 2^20
columns: 2^14
And filldown.
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