Repeat for number of selected cells on spreadsheet?

Questions & Suggestions
1

I have to collate a number of spreadsheets created by outside entities. Our standard is to notate expenditures as negative numbers. Some of the outside spreadsheets notate them as positive numbers.

I've made a macro to convert them to negative numbers in the spreadsheet with a single button. Now I'd like to use a Repeat function to convert multiple cells.

If I select a number of contiguous cells, how can I count them and use that count to control the Repeat function? I thought I'd copy the cells to the clipboard, and then count the items in the clipboard, but I haven't been able to find a way to do that.

Thanks in advance for your help.

2

If you copy them, and paste them in to a raw text editor (eg BBEdit), what do you get?

Can you count the lines? If so, filtering the clipboard with the Line Count filter should get you the desired number.

3

Ha! BBEdit shows the lines. Thanks, Peter.

4

OK, then the Filter action should get the right result.

5

Worked like a charm. There's a certain undeniable delight in watching KM automate through a bunch of scutwork.

Many thanks!

1 Like
6

@peternlewis' method may be better for your use case, but JIC it is very easy to get a count of cells from the spreadsheet app:

tell application "Microsoft Excel"
  set numCells to count of (cells of selection)
end tell
return numCells

And, if you want to take it one step further using AppleScript, you can easily change the sign of the selected cells in Excel:

tell application "Microsoft Excel"
  set cellList to (cells of selection)
  
  repeat with oCell in cellList
    set curVal to value of oCell
    set value of oCell to -1.0 * curVal
  end repeat
end tell
1 Like
7

Thanks!