Replies

Hans,

My apologies. You’re absolutely correct. And thanks for the extra effort on your part.

regards

Geoffrey

Hans,

Thank you SO much; so quick and such good value. When I used it, it seemed to copy, not move. If that’s not my fault, I’m sure that’s something that I could easily learn and repair.

regards

Geoffrey

Sam,

This looks perfect to me: just what I wanted. I’ll spend some time investigating it. Thanks to both you and Hans.

regards

Geoffrey

Hans,

Thank you for your comments. In more detail I have a Macros 4 function which looks like

ZSheetProtect(Sheet,contents,windows,password,objects,scenarios)

where

Hoping I’m not too late here, but I have a similar problem (wanting to count and sum rows in a table that match criteria based on the contents of a number of columns). I know I’m a bit slow on array formulae, so I looked for an alternative. I found DCOUNT. This function references a table (for the counting), and also a Criteria Table. This Criteria Table can check on multiple criteria (I think it’s the same as the Criteria Tables in Advanced FILTER (Data / Filter / Advanced Filter )). So, for example, using Conditional Criteria I could check on the number of characters in cells in a column, whether a column cell contained a certain character, etc, etc. Moreover I can do OR’s and ANDs. Thus I can select the rows to count. Using DSUM I can total a column in a similar way. I’m hoping these techniques will solve my problems. Perhaps it could also provide relevant alternatives to using SUMPRODUCT and Array Formulae.

regards

Geoffrey Howell

Dear Legare,

Thank you for your help. I think I have omitted some information that may be important. At times I need to export or import these character strings to/from other applications and the best way I know to do this with flexibility is via the clipboard. Being on the clipboard as text gives the clipboard greater longevity and resistance to Excel events. A lot of the work I do is with legacy Macros 4 language (hence “Antediluvian”), and I also find difficulty in moving text from a single cell to a single but merged cell, even using “FORMULA”. Thus my necessary forays into VB. Thanks again for your assistance.

regards

Geoffrey