how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P
osheet:cells(xloop,17):value=debits && Q
osheet:cells(xloop,18):value=credits && R
osheet:cells(xloop,19):value="This Is The TaxCredit" && S osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:

how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P
osheet:cells(xloop,17):value=debits && Q
osheet:cells(xloop,18):value=credits && R
osheet:cells(xloop,19):value="This Is The TaxCredit" && S osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

thetax := osheet:cells(xxx,yyy):value
@ rr,cc say thetax

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:

how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P
osheet:cells(xloop,17):value=debits && Q
osheet:cells(xloop,18):value=credits && R
osheet:cells(xloop,19):value="This Is The TaxCredit" && S osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value

Looks to me like you need to read the value first

xValue := oSheet:Range("T"+alltrim(str(xloop))
if xValue > 0
OutComment := "cr"
elseif xValue = 0
OutComment := "dr"
else
OutComment := "error" // <<maybe some other value here>>
endif

oSheet:cells(xLoop,21):Value = originalbalance
oSheet:cells(xLoop,21+1):value = OutComment

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:

how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P
osheet:cells(xloop,17):value=debits && Q osheet:cells(xloop,18):value=credits && R osheet:cells(xloop,19):value="This Is The TaxCredit" && S osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

Firstly you are trying to compare a string to be greater than another string, I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value

Looks to me like you need to read the value first

xValue := oSheet:Range("T"+alltrim(str(xloop))
if xValue > 0
OutComment := "cr"
elseif xValue = 0
OutComment := "dr"
else
OutComment := "error" // <<maybe some other value here>>
endif

oSheet:cells(xLoop,21):Value = originalbalance
oSheet:cells(xLoop,21+1):value = OutComment

thanks poopall:

'Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
-that e.g. was just to get the syntax.

'thetax := osheet:cells(xxx,yyy):value'
-this helps me a lot.

in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
-just wondering why osheet:'Range' when i need the value from just a single cell?

can't i just code (inside the loop):
thetax := osheet:cells(xloop,20):value
oSheet:cells(xLoop,21):Value = thetax
...
...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, January 11, 2022 at 1:45:56 PM UTC+5:30, timepro timesheet wrote:

On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:

how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P osheet:cells(xloop,17):value=debits && Q osheet:cells(xloop,18):value=credits && R osheet:cells(xloop,19):value="This Is The TaxCredit" && S osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value

Looks to me like you need to read the value first

xValue := oSheet:Range("T"+alltrim(str(xloop))
if xValue > 0
OutComment := "cr"
elseif xValue = 0
OutComment := "dr"
else
OutComment := "error" // <<maybe some other value here>>
endif

oSheet:cells(xLoop,21):Value = originalbalance oSheet:cells(xLoop,21+1):value = OutComment

thanks poopall:

'Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
-that e.g. was just to get the syntax.

'thetax := osheet:cells(xxx,yyy):value'
-this helps me a lot.

in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
-just wondering why osheet:'Range' when i need the value from just a single cell?

can't i just code (inside the loop):
thetax := osheet:cells(xloop,20):value
oSheet:cells(xLoop,21):Value = thetax
...
...

also:

what i am trying to achieve is this:

e.g. if cell(A824) is sum(A8..A823), then if any value in cells A8 - A823 is edited, the value in A824 "auto changes"...

for xxx=nstart to nend
how do i code cell(21,xxx) should "auto change" to 'paid' or 'non paid' or whatever...on the basis of cell(20,xxx) value.
(if value of cell(20,xxx)<0, then cell(21,xxx) must show 'paid', if cell(20,xxx)>0 then cell(21,xxx)...'unpaid'
-similar algorithm as sum()
next
so, if the user (on the open .xlsx file), 'manually' changes the value in cell(20,xxx) then 'correspondingly' cell(21,xxx) should auto change...
cell(21,xxx) has to be blocked for manual edit/input.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, January 11, 2022 at 2:24:12 PM UTC+5:30, timepro timesheet wrote:

On Tuesday, January 11, 2022 at 1:45:56 PM UTC+5:30, timepro timesheet wrote:

On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:

how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P osheet:cells(xloop,17):value=debits && Q osheet:cells(xloop,18):value=credits && R osheet:cells(xloop,19):value="This Is The TaxCredit" && S osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value

Looks to me like you need to read the value first

xValue := oSheet:Range("T"+alltrim(str(xloop))
if xValue > 0
OutComment := "cr"
elseif xValue = 0
OutComment := "dr"
else
OutComment := "error" // <<maybe some other value here>>
endif

oSheet:cells(xLoop,21):Value = originalbalance oSheet:cells(xLoop,21+1):value = OutComment

thanks poopall:

'Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
-that e.g. was just to get the syntax.

'thetax := osheet:cells(xxx,yyy):value'
-this helps me a lot.

in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
-just wondering why osheet:'Range' when i need the value from just a single cell?

can't i just code (inside the loop):
thetax := osheet:cells(xloop,20):value
oSheet:cells(xLoop,21):Value = thetax
...
...

also:

what i am trying to achieve is this:

e.g. if cell(A824) is sum(A8..A823), then if any value in cells A8 - A823 is edited, the value in A824 "auto changes"...

for xxx=nstart to nend
how do i code cell(21,xxx) should "auto change" to 'paid' or 'non paid' or whatever...on the basis of cell(20,xxx) value.
(if value of cell(20,xxx)<0, then cell(21,xxx) must show 'paid', if cell(20,xxx)>0 then cell(21,xxx)...'unpaid'
-similar algorithm as sum()
next
so, if the user (on the open .xlsx file), 'manually' changes the value in cell(20,xxx) then 'correspondingly' cell(21,xxx) should auto change...
cell(21,xxx) has to be blocked for manual edit/input.

how to use a variable with/in sum() or formula()

a1="E"+alltrim(str(xrow,6))
a2="J"+alltrim(str(xrow,6));oSheet:cells(xrow,20) a3="N"+alltrim(str(xrow,6));oSheet:cells(xrow,20)
:Formula = "=((&a1+&a2)/(&a3))*taxpercent" && in process it does not pick the value of taxpercent (can be diff. for each year)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, 11 January 2022 at 7:15:56 pm UTC+11, timec...@gmail.com wrote:

On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:

how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P osheet:cells(xloop,17):value=debits && Q osheet:cells(xloop,18):value=credits && R osheet:cells(xloop,19):value="This Is The TaxCredit" && S osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value

Looks to me like you need to read the value first

xValue := oSheet:Range("T"+alltrim(str(xloop))
if xValue > 0
OutComment := "cr"
elseif xValue = 0
OutComment := "dr"
else
OutComment := "error" // <<maybe some other value here>>
endif

oSheet:cells(xLoop,21):Value = originalbalance oSheet:cells(xLoop,21+1):value = OutComment

thanks poopall:

'Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
-that e.g. was just to get the syntax.

'thetax := osheet:cells(xxx,yyy):value'
-this helps me a lot.

in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
-just wondering why osheet:'Range' when i need the value from just a single cell?

can't i just code (inside the loop):
thetax := osheet:cells(xloop,20):value
oSheet:cells(xLoop,21):Value = thetax
...
...

A range can be a single cell, so use oSheet:Range("T"+alltrim(str(xloop)) tro extract the value

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, 11 January 2022 at 7:54:12 pm UTC+11, timec...@gmail.com wrote:

On Tuesday, January 11, 2022 at 1:45:56 PM UTC+5:30, timepro timesheet wrote:

On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:

how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P osheet:cells(xloop,17):value=debits && Q osheet:cells(xloop,18):value=credits && R osheet:cells(xloop,19):value="This Is The TaxCredit" && S osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value

Looks to me like you need to read the value first

xValue := oSheet:Range("T"+alltrim(str(xloop))
if xValue > 0
OutComment := "cr"
elseif xValue = 0
OutComment := "dr"
else
OutComment := "error" // <<maybe some other value here>>
endif

oSheet:cells(xLoop,21):Value = originalbalance oSheet:cells(xLoop,21+1):value = OutComment

thanks poopall:

'Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
-that e.g. was just to get the syntax.

'thetax := osheet:cells(xxx,yyy):value'
-this helps me a lot.

in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
-just wondering why osheet:'Range' when i need the value from just a single cell?

can't i just code (inside the loop):
thetax := osheet:cells(xloop,20):value
oSheet:cells(xLoop,21):Value = thetax
...
...

also:

what i am trying to achieve is this:

e.g. if cell(A824) is sum(A8..A823), then if any value in cells A8 - A823 is edited, the value in A824 "auto changes"...

for xxx=nstart to nend
how do i code cell(21,xxx) should "auto change" to 'paid' or 'non paid' or whatever...on the basis of cell(20,xxx) value.
(if value of cell(20,xxx)<0, then cell(21,xxx) must show 'paid', if cell(20,xxx)>0 then cell(21,xxx)...'unpaid'
-similar algorithm as sum()
next
so, if the user (on the open .xlsx file), 'manually' changes the value in cell(20,xxx) then 'correspondingly' cell(21,xxx) should auto change...
cell(21,xxx) has to be blocked for manual edit/input.

Use the sum formula in a824, excel will calculate any changes
oSheet:Cells( "A824" ):Formula = "=sum(a8:A823)", I am sure that I have mentioned this in previous posts

how do i code cell(21,xxx) should "auto change" to 'paid' or 'non paid' or whatever...on the basis of cell(20,xxx) value.

I think you are trying to learn the basic of Excel and Harbour at the same time

oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"

cell(21,xxx) has to be blocked for manual edit/input.
Would require you to protect the spreadsheet with a password, and then unlock the cell..range eg 21,xxx with a password.. I have not tried this programmatically within xharbour so cant assist.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

thanks poopall:

'I think you are trying to learn the basic of Excel and Harbour at the same time '
-pretty much...

' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"' -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
doe not work. (is my coding/syntax wrong)

also,
how to use a VARIABLE with/in sum() or formula()
a1="E"+alltrim(str(xrow))
a2="J"+alltrim(str(xrow))
a3="N"+alltrim(str(xrow))
:Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, 14 January 2022 at 4:47:19 pm UTC+11, PARESH VALIA wrote:

thanks poopall:

'I think you are trying to learn the basic of Excel and Harbour at the same time '
-pretty much...

' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"' -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
doe not work. (is my coding/syntax wrong)

also,
how to use a VARIABLE with/in sum() or formula()
a1="E"+alltrim(str(xrow))
a2="J"+alltrim(str(xrow))
a3="N"+alltrim(str(xrow))
:Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)

"=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )


Do this
oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'

I hav'nt tested so cant guarantee it will work but you need to build the xHarbour string to include what you need for the Excel formulae

Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT"

This is not an excel formula you so can't substitute XHarbour code into Excel code and visa versa

Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"

I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, January 14, 2022 at 12:07:18 PM UTC+5:30, poopall wrote:

On Friday, 14 January 2022 at 4:47:19 pm UTC+11, PARESH VALIA wrote:

thanks poopall:

'I think you are trying to learn the basic of Excel and Harbour at the same time '
-pretty much...

' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"' -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
doe not work. (is my coding/syntax wrong)

also,
how to use a VARIABLE with/in sum() or formula()
a1="E"+alltrim(str(xrow))
a2="J"+alltrim(str(xrow))
a3="N"+alltrim(str(xrow))
:Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)

"=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )
Do this
oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'

I hav'nt tested so cant guarantee it will work but you need to build the xHarbour string to include what you need for the Excel formulae

Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT"

This is not an excel formula you so can't substitute XHarbour code into Excel code and visa versa

Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"

I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.

thanks again poopall:

'oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'
--had tried that too...but doesn't work.

' I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.'
Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"
--no, it is initially defined as a public variable in main.prg. then, in the do whil !eo()...skip...endd, ntaxpercent value may vary for EACH CELL , on the basis of the 'billyear field' inside the loop after each skip.

in the loop
if field->billyear=2017; ntaxpercent=field->billtaxperc ntaxpercent=if(ntaxpercent=0,1,ntaxpercent)
e.g. ntaxpercent=15 - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTaxpercent" (i.e. 15)
skip
if field->billyear=2015; ntaxpercent=field->billtaxperc
ntaxpercent is 18 - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTaxpercent" (i.e. 18)
skip
if field->billyear=2021; ntaxpercent=field->billtaxperc
ntaxpercent is nXYZ - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nXYZ (i.e...)
skip
...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, 14 January 2022 at 12:07:18 UTC+5:30, poopall wrote:

On Friday, 14 January 2022 at 4:47:19 pm UTC+11, PARESH VALIA wrote:

thanks poopall:

'I think you are trying to learn the basic of Excel and Harbour at the same time '
-pretty much...

' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"' -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
doe not work. (is my coding/syntax wrong)

also,
how to use a VARIABLE with/in sum() or formula()
a1="E"+alltrim(str(xrow))
a2="J"+alltrim(str(xrow))
a3="N"+alltrim(str(xrow))
:Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)

"=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )
Do this
oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'

I hav'nt tested so cant guarantee it will work but you need to build the xHarbour string to include what you need for the Excel formulae

Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT"

This is not an excel formula you so can't substitute XHarbour code into Excel code and visa versa

Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"

I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.

thanks again poopall:

'oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'
--had tried that too...but doesn't work.

' I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.'
Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"
--no, it is initially defined as a public variable in main.prg. then, in the do whil !eo()...skip...endd, ntaxpercent value may vary on the basis of the 'billyear field' inside the loop after each skip.

in the loop
if field->billyear=2017; ntaxpercent=field->billtaxperc
ntaxpercent is 15 - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*15"
skip
if field->billyear=2015; ntaxpercent=field->billtaxperc
ntaxpercent is 18 - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*18"
skip
if field->billyear=2021; ntaxpercent=field->billtaxperc
ntaxpercent is nXYZ - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nXYZ
skip
...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, January 14, 2022 at 12:07:18 PM UTC+5:30, poopall wrote:

On Friday, 14 January 2022 at 4:47:19 pm UTC+11, PARESH VALIA wrote:

thanks poopall:

'I think you are trying to learn the basic of Excel and Harbour at the same time '
-pretty much...

' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"' -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
doe not work. (is my coding/syntax wrong)

also,
how to use a VARIABLE with/in sum() or formula()
a1="E"+alltrim(str(xrow))
a2="J"+alltrim(str(xrow))
a3="N"+alltrim(str(xrow))
:Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)

"=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )
Do this
oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'

I hav'nt tested so cant guarantee it will work but you need to build the xHarbour string to include what you need for the Excel formulae

Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT"

This is not an excel formula you so can't substitute XHarbour code into Excel code and visa versa

Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"

I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.

thanks, this works

'Do this
oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'

--oSheet:Cells(xxx,12):Formula='=if(K'+alltrim(str(xxx))+'="CR","Paid",if(K'+alltrim(str(xxx))+'="DE","Defaulted","UnPaid"))'

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)