I have a routine that runs if a condition is “=” to or “>” than and what I want to do is pass the symbol to an " if then" construct and I cannot work out how to do that.
This sounds like a very dumb question to me but I cannot figure it out.
Thanks
Peter
Can we take a look at your routine, or is it a big secret? Your request is interesting, but not clear.
Somewhere in Code Exchange I have already seen the construction of logical structures.
Not a big secret but the code presently covers quite a few pages so I tried to be precise. This is the truncated part. You can see at present I deal with my issue by repeating the code once for equal and again when it is not. Thanks again for considering my issue.
Peter
tell application "Microsoft Excel"
activate
set UsedRows to count rows of used range of active sheet
set UsedColumns to count columns of used range of active sheet
set T1 to 0
repeat 2 times
repeat with Rn from 3 to UsedRows
if T1 = 0 then
if value of cell ("S" & Rn) > 1 then
set hidden of row (Rn) to true
end if
else if value of cell ("S" & Rn) = 1 then
set hidden of row (Rn) to true
end if
end repeat
set hidden of (entire column of (range "B:C,G:G,K:M,O:R,T:T,V:V,Y:Z,AB:AJ")) to true
set FundsInvested to 0
set ProductToDay to 0
set WResultExp to 0
set ProductExpDay to 0
set DaystoExp to 0
set DaysToToday to 0
set StockCount to 0
repeat with Rn from 3 to UsedRows
if value of cell ("S" & Rn) = 1 then --Stock Ranked 1
set StockCount to StockCount + 1
set NoStock to ((10000 / (value of cell ("H" & Rn))) div 100) * 100
set ProductExpDay to ProductExpDay + (value of cell ("H" & Rn)) * ((((value of cell ("I" & Rn)) - (value of cell ("AK" & Rn)))) / 86400) * NoStock
set DaystoExp to DaystoExp + ((((value of cell ("I" & Rn)) - (value of cell ("AK" & Rn)))) / 86400)
set FundsInvested to FundsInvested + ((value of cell ("H" & Rn)) - ((value of cell ("N" & Rn)) + (value of cell ("G" & Rn)))) * NoStock
set WResultExp to WResultExp + (((value of cell ("J" & Rn)) + (value of cell ("G" & Rn)) + (value of cell ("N" & Rn))) - (value of cell ("H" & Rn))) * NoStock --At Expiry
set StockYieldExp to ((WResultExp / (ProductExpDay / 365) / 100)) * 100
else if value of cell ("S" & Rn) > 1 and T1 = 1 then
set StockCount to StockCount + 1
set NoStock to ((10000 / (value of cell ("H" & Rn))) div 100) * 100
set ProductExpDay to ProductExpDay + (value of cell ("H" & Rn)) * ((((value of cell ("I" & Rn)) - (value of cell ("AK" & Rn)))) / 86400) * NoStock
set DaystoExp to DaystoExp + ((((value of cell ("I" & Rn)) - (value of cell ("AK" & Rn)))) / 86400) --Total Days
set FundsInvested to FundsInvested + ((value of cell ("H" & Rn)) - ((value of cell ("N" & Rn)) + (value of cell ("G" & Rn)))) * NoStock
set WResultExp to WResultExp + (((value of cell ("J" & Rn)) + (value of cell ("G" & Rn)) + (value of cell ("N" & Rn))) - (value of cell ("H" & Rn))) * NoStock --At Expiry
set StockYieldExp to ((WResultExp / (ProductExpDay / 365) / 100)) * 100
end if
end repeat
#########
set T1 to 1
end repeat
set hidden of column ("1:" & UsedColumns) to false
end tell
This is not what I thought. You just need to apply the parentheses correctly:
tell application "Microsoft Excel"
activate
tell (used range of active sheet) to set {UsedRows, UsedColumns} to {count rows, count columns}
set T1 to 0
set {FundsInvested, ProductToDay, ProductExpDay, DaystoExp, DaysToToday, StockCount} to {0, 0, 0, 0, 0, 0}
repeat 2 times
repeat with Rn from 3 to UsedRows
if ((value of cell ("S" & Rn) > 1) and (T1 = 0)) or (value of cell ("S" & Rn) = 1) then ¬
set hidden of row (Rn) to true
end repeat
set hidden of (entire column of (range "B:C,G:G,K:M,O:R,T:T,V:V,Y:Z,AB:AJ")) to true
repeat with Rn from 3 to UsedRows
if ((value of cell ("S" & Rn) > 1) and (T1 = 1)) or (value of cell ("S" & Rn) = 1) then --THIS
set StockCount to StockCount + 1
set NoStock to ((10000 / (value of cell ("H" & Rn))) div 100) * 100
set ProductExpDay to ProductExpDay + (value of cell ("H" & Rn)) * ((((value of cell ("I" & Rn)) - (value of cell ("AK" & Rn)))) / 86400) * NoStock
set DaystoExp to DaystoExp + ((((value of cell ("I" & Rn)) - (value of cell ("AK" & Rn)))) / 86400)
set FundsInvested to FundsInvested + ((value of cell ("H" & Rn)) - ((value of cell ("N" & Rn)) + (value of cell ("G" & Rn)))) * NoStock
set WResultExp to WResultExp + (((value of cell ("J" & Rn)) + (value of cell ("G" & Rn)) + (value of cell ("N" & Rn))) - (value of cell ("H" & Rn))) * NoStock --At Expiry
set StockYieldExp to ((WResultExp / (ProductExpDay / 365) / 100)) * 100
end if
end repeat
#########
set T1 to 1
end repeat
set hidden of column ("1:" & UsedColumns) to false
end tell
Hi
Once again I did not explain the issue correctly. My post as shown works. I had to resort to the
‘T1’ approach at the start of the routine instead of using = or >, where I am hiding the rows. Does that make sense.
I tried to understand your algorithm without testings. From your code, you calculate for the value > 1 only second path, an for value=1 both paths. Maybe, this is what you look for:
tell application "Microsoft Excel"
activate
tell (used range of active sheet) to set {UsedRows, UsedColumns} to {count rows, count columns}
set hidden of (entire column of (range "B:C,G:G,K:M,O:R,T:T,V:V,Y:Z,AB:AJ")) to true
set {NoStock, ProductExpDay, DaystoExp, FundsInvested, WResultExp, StockYieldExp, StockCount} to {0, 0, 0, 0, 0, 0, 0}
repeat with Rn from 3 to UsedRows
set theValue to value of cell ("S" & Rn)
if theValue ≥ 1 then set hidden of row (Rn) to true
if theValue = 1 then set {NoStock, ProductExpDay, DaystoExp, FundsInvested, WResultExp, StockYieldExp, StockCount} to my calc(Rn, NoStock, ProductExpDay, DaystoExp, FundsInvested, WResultExp, StockYieldExp, StockCount)
if theValue ≥ 1 then set {NoStock, ProductExpDay, DaystoExp, FundsInvested, WResultExp, StockYieldExp, StockCount} to my calc(Rn, NoStock, ProductExpDay, DaystoExp, FundsInvested, WResultExp, StockYieldExp, StockCount)
end repeat
set hidden of column ("1:" & UsedColumns) to false
end tell
on calc(Rn, NoStock, ProductExpDay, DaystoExp, FundsInvested, WResultExp, StockYieldExp, StockCount)
tell application "Microsoft Excel"
set cellHvalue to value of cell ("H" & Rn)
set cellIvalue to value of cell ("I" & Rn)
set cellGvalue to value of cell ("G" & Rn)
set cellAKvalue to value of cell ("AK" & Rn)
set cellNvalue to value of cell ("N" & Rn)
set cellJvalue to value of cell ("J" & Rn)
end tell
set NoStock to ((10000 / cellHvalue) div 100) * 100
set ProductExpDay to ProductExpDay + cellHvalue * ((cellIvalue - cellAKvalue) / 86400) * NoStock
set DaystoExp to DaystoExp + (cellIvalue - cellAKvalue) / 86400
set FundsInvested to FundsInvested + cellHvalue - (cellNvalue + cellGvalue) * NoStock
set WResultExp to WResultExp + ((cellJvalue + cellGvalue + cellNvalue) - cellHvalue) * NoStock --At Expiry
set StockYieldExp to ((WResultExp / (ProductExpDay / 365) / 100)) * 100
set StockCount to StockCount + 1
return {NoStock, ProductExpDay, DaystoExp, FundsInvested, WResultExp, StockYieldExp, StockCount}
end calc
Thank you once again, I still do no think I am describing what I want to achieve… Maybe this approach will make it clearer.
[appleScript]
tell application “Microsoft Excel”
set UsedRows to count rows of used range of active sheet
–In my worksheet Column S has a numeral 1 through 5
–I want to process the data based on column S once if its 1 and then if its greater than 1
–What I have tried is setting a variable to either “=” or “>”
–so
set Sy to “=”
repeat 2 times
repeat with Rn from 3 to UsedRows
–Then I used the variable like this
–If value of cell(“S” & Rn) Sy 1 then. Which of course does not work
if value of cell (“S” & Rn) = 1 then
–Calculate
end if
end repeat
set Sy to “>”
end repeat
end tell
:rolleyes:
Passing the condition symbol example:
set Sy to " = "
repeat 2 times
repeat with value from 1 to 5
if run script (value as text) & Sy & "1" then
display dialog (value as text) & " is " & Sy & " 1"
end if
end repeat
set Sy to " > "
end repeat
Perfect thank you very much. I have never seen the “run script” parameter. I assume that’s what turns it into a boolean expression.
Just in case I may have missed something this is my revised script with your solution.
tell application "Microsoft Excel"
set UsedRows to count rows of used range of active sheet
--In my worksheet Column S has a numeral 1 through 5
--I want to process the data based on column S once if its 1 and then if its greater than 1
set Sy to "="
repeat 2 times
repeat with Rn from 7 to 8
if run script ((value of cell ("S" & Rn)) as text) & Sy & "1" then
display dialog Rn & " is " & Sy & " 1" as string
end if
end repeat
set Sy to ">"
end repeat
end tell
Thank you
Peter
Nigel I am truly thick. Once I click on the AppleScript option I have been splitting the start & end hence the capitalization. Hopefully I will remember in future.
Peter