Excel Scripts

[mrrlg 0]

I have an excel spreadsheet with the following equation in one column, =sum(g:42)*2 where the second number will range from 1 to 24. I would like to be able to write a script that would decrease the second number by 1 in each cell in the column where that number exists. Is this something that AutoIt can do?

[drak 0]

I think if you put the second number in another cell like this:

=sum(g:42)*h42

then use Autoit to edit h42 by sending keystrokes that should work

look at "Send" in your help files, it also helps if you know what the keystrokes in Excel are.

[Chris_1013 0]

Surely this sorta thing would be better through a formula in Excel, or maybe even just a fill down, rather than AutoIt-ing it.

[Bartokv 1]

Surely this sorta thing would be better through a formula in Excel, or maybe even just a fill down, rather than AutoIt-ing it.

I agree.

Although I'm not sure that Excel Forumlas themselves are versitile enough to do what mrrlg's asking. However I believe that most versions of Excel should be equipped with a VBA Macro editor, and this should be easy to do within a macro.

[drak 0]

it depends on how much experience one has with each product, VBA is not that easy to understand for some ppl.

[ivan 1]

It's been ages since I last used excel, but I doubt whether the expression:

=sum(g:42)*h42

is any different from

g42*h42

The reason is that the expression sum is used to add arrays rather than single cells, I might be wrong though.

An interesting alternative solution is to create an intermediate column to do the firts part of your operation and a second column to decrease the value by 1. you may then hide the intermediate column by grouping it. Both can be done manually or automated with autoit. I believe I've done something similar ages ago.

IVAN

Edited August 12, 2004 by ivan