for each in worksheet

[michaelk]

I've been looking everywhere for this and I can't find it.

What is the syntax for addressing every cell in a range of selected cells? I'm using GetWSSelection to get the selected cells.

Is it something like

FOR (toprangerow,leftrangecolumn...bottomrangerow,rightrangecolumn)

DO something;

If only one cell is selected will the FOR statement still work?

Thanks

mk

ps. where should I have been looking for the correct syntax?

[James Russell]

Michael,

I swear I've used this in a script somewhere but for the life of me can't find it. I think in my application it was for Misc auto numbering on a spreadsheet.

In my head it would have gone like this:

VAR

Incval, *all other ints down there* :INTEGER;

Begin

GetWSSelection(WSH,CR,CC,TRR,TRC,TRS,BRR,BRC,BRS);

Incval:=0;

{----Loop begins----}

While Incval+TRC

Begin

*edits to cells as required*

Incval:= Incval+1;

End;

{--------------------------}

I think that's the approach I took but if I find an example I'll let you know.

Hope that gets you somewhere, I'd appreciate if the higher VS coders had different approaches it'd be good to learn some.

J

[michaelk]

This seems to work. Is there an easier way that I'm not seeing?

Procedure DoAllCellsInRange;

{Badly scripted by Michael Klaers}

VAR

WSHand : Handle;

currentCellRow,currentCellColumn,topRangeRow,leftRangeColumn,topRangeSubrow,bottomRangeRow,rightRangeColumn,bottomRangeSubrow :INTEGER;

LoopColumn,LoopRow :INTEGER;

BEGIN

WSHand:=GetTopVisibleWS;

GetWSSelection(WSHand,currentCellRow,currentCellColumn,topRangeRow,leftRangeColumn,topRangeSubrow,bottomRangeRow,rightRangeColumn,bottomRangeSubrow);

For LoopColumn := leftRangeColumn TO rightRangeColumn

DO BEGIN

FOR LoopRow := topRangeRow TO bottomRangeRow

DO BEGIN

Message(LoopColumn,' ',LoopRow);

Wait(2);

END;

END;

END;

RUN(DoAllCellsInRange);

[Pat Stanford]

Michael,

What you have should allow you to access every cell in a range individually. My question is why do you need to do this?

I think most of the attribute functions already take the range and operate on it.

About the only situation I can think of is if you are manually entering formulas.

Tell us more about the desired outcome instead of asking about the technique.

[michaelk]

Pat

Sometimes I want to change just one format, say make text bold. But if the range of cells has cells with plain text, italics, different font sizes etc., then all of those settings get unified if I make the whole selection bold.

mk

[Pat Stanford]

Makes sense.

I think I would probably use a dialog box sort of like the Duplicate Array dialog box to get the settings you want to change for the range of cells.

Then use your nested loops above to loop through each cell, read the current values, change the items that need to be changed and then write the data back to the cell.

I would probably create a small array (2 by however many parameters there are) or at least double variables. One variable to tell whether that parameter needs to change and one to hold the new value. The variable to tell whether the parameter should be changed Then the code could be something like:

GetWSCellFill(H1,ThisRow,ThisColumn,InStyle,InBGColor,InFGColor,InFill);

If ChangeStyle then OutStyle:=NewStyle else OutStyle:=InStyle;

if ChangeBGColor then OutBGColor:=NewBGColor else OutBGColor:=InBGColor;

etc..

SetWSCellFill(H1,ThisRow,ThisColumn,ThisRow,ThisColumn,OutStyle,OutBGColor,OutFGColor,OutFill);

This gets pretty long by the time you do all the possible parameters, but each line is pretty simple.

The other possibility would be to create a big array with rows for each cell and columns for each possible setting.

Loop through all the cells once to read in the original data to the array.

Change the cells in the array that need to be updated.

Loop through all the cells and write back the data from the updated array.

Both ways would work. I don't know if there would be a speed advantage to either way.

[James Russell]

Michael,

Just a word of warning from prior death by tables.

I thought it was cool to do mega loops involving mass data changes, line styles and font.

Firstly, when I read from Pat about SetWSAutoRecalcState in regards to worksheet scripting was the happiest week for a while. Cutting creation time to a tenth of previously. You're probably all over it but never hurts to reiterate for others.

Secondly, the moment I started to do mass changes when I had larger numbers of database lookups with more subrows everything slows exponentially. When I run one of my scripts on a table which has somewhere between 500 and 4000 database lookups, (don't ask why, it's the crazy!) it normally completes in around 15 seconds when none of them have data. When there are approximately 200 databases containing data the script time increases to around 3 mins, 1000 containing data, 8 mins.

I'm sure your databases/worksheets are nothing quite this radical but just as fair warning make your scripting efficient, your tables efficient and apply changes earlier if possible.

That's just a little of my past year worth of database-script interaction.

J