michaelk Posted August 15, 2011 Share Posted August 15, 2011 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? Quote Link to comment
James Russell Posted August 16, 2011 Share Posted August 16, 2011 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 Quote Link to comment
michaelk Posted August 16, 2011 Author Share Posted August 16, 2011 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); Quote Link to comment
Pat Stanford Posted August 17, 2011 Share Posted August 17, 2011 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. Quote Link to comment
michaelk Posted August 17, 2011 Author Share Posted August 17, 2011 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 Quote Link to comment
Pat Stanford Posted August 17, 2011 Share Posted August 17, 2011 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. Quote Link to comment
James Russell Posted August 18, 2011 Share Posted August 18, 2011 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 Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.