Passing Symbols

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