0

I decided to re-write my question to simplify what I'm after. I came across my first post on Stack about the same problem I'm having now. That's when I added all the INDIRECT's to the code in this original question.
Sum with substitute, ignore and a dynamic range

I never was able to make the "*" wildcard mentioned in the linked post work. Maybe having non-English Excel come into play here?

In it's simplest form, could you help me rewrite this code (not working):

Range("namedRange").FormulaR1C1 = "=SUMPRODUCT(SUBSTITUTE(R10C:R[-1]C,""S"",""""))"

To do the following:

  1. Replace all "S'es" with blank ("4S" would read "4" while summing)
  2. Ignore all blank cells and cells containing other text than "S". "4T" should be ignored.
  3. Sum the numbers in range
  4. (Cell value will never be "4SK", so ok to fail on that)

I might be OK to write a function to do all of the above instead. But right now I'm stuck and my Google results leave me non the wiser. Any help would still be really appreciated

Doons
  • 183
  • 11
  • As you see, the maintenance of your formula is too complicated. Therefore I would change the system to use code to produce the results, not the formula. On the long run you will save time an d increase users' efficiency. – Variatus Jan 10 '21 at 00:45
  • Any time I see INDIRECT used, I shudder. Consider OFFSET() as an alternative. – DS_London Jan 10 '21 at 08:07
  • Also, do you want to only strip out the number if certain letters are present (ie a predefined list of acceptable characters: so “4S” would yield “4”, but “2Q” would yield “”), or just strip out any letters (or non-numbers)? – DS_London Jan 10 '21 at 08:16
  • @DS_London you do know Offset is volatile too right? It's not any better than Indirect. Offset can almost always be replaced with Index but Indirect can't always be replaced, unless you make a non-volatile version in a UDF. – Simon Jan 10 '21 at 08:41
  • Is the number always first? If so instead of substituting you can just do a `LEFT()` command. If sometimes there is 2 digits instead of 1 you can include a `ISNUMBER` to check the second character using `MID()` – Simon Jan 10 '21 at 08:50
  • Thanks for all your input. Indeed INDIRECT messes with the performance of the sheet. So best to leave it out. I re-worded my question after figuring out my basic needs. Maybe it's easier to help me now? – Doons Jan 10 '21 at 15:03
  • 1
    @Doons I have edited my answer below to handle your now different problem – Ron Rosenfeld Jan 10 '21 at 17:48
  • @Simon The major advantage of OFFSET over INDIRECT for me is that you can add/remove rows and columns and your cell references stay intact. Otherwise you are defeating all the clever stuff that Excel does to preserve your formulae as the sheet layout changes. – DS_London Jan 10 '21 at 18:45
  • @DS_London well if that's your preference more to you but that can easily be done with INDEX to make it non-volatile which is why I never use OFFSET. I would use INDIRECT though for cell sheet references which is unfortunately the only formula that can do it. Would be nice if they could somehow a non-volatile version. Anyway lets not hijack this persons question. We all have our preferences. – Simon Jan 10 '21 at 18:53

1 Answers1

0

Edited in line with your edited question

I think a UDF would be much simpler to maintain.

I used a Regex as your requirements seem to be changing, and this will allow more flexibility in modifying it in the future.

All you need to do is pass the range which you want to process to the function as it's argument; which you can do either using VBA, or directly on the worksheet.

Here is one that returns the sum of all cells that are either numeric, or have a single S.

If there might be more than a single S in a cell, then change the .Global parameter to True.

It allows for non-contiguous ranges.

If your ranges will always be contiguous, you can eliminate the outer loop.

Option Explicit
Function sumPlusS(rg As Range) As Long
    Dim vData, w, s As String, a As Range
    Dim mySum As Long
    Const sPat As String = "S"
    
Dim RE As Object
    
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = False 'looking only for a single letter, change to True if could have multiple S's
    .ignorecase = False 'case sensitive
    .Pattern = sPat
End With
'read into variant array for processing

For Each a In rg.Areas 'allow for non-contiguous ranges

    vData = a

    'iterate through the array and sum the values
    For Each w In vData
        s = RE.Replace(w, "")
        If IsNumeric(s) Then mySum = mySum + Val(s)
    Next w
Next a

sumPlusS = mySum
    

End Function
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thank you very much for taking the time to help me fix this problem! Sorry for having you re-write your answer! May I ask if it matters to `set RE = nothing` at the end? I will have this function in 20 columns at the same time. – Doons Jan 10 '21 at 18:31
  • BTW. Just to understand the code I tried adding "K" to the pattern. Just for testing. I don't seem to understand your comment on how to do that. Constants can't be arrays I'm told and `dim sPat as Variant` then `sPat = Array("S","K") doesn't work. Could you provide some quick guidance here? To future proof my code :p – Doons Jan 10 '21 at 18:41
  • @Doons `RE` will be set to nothing when the function terminates, so there is no need to explicitly set it. – Ron Rosenfeld Jan 10 '21 at 19:01
  • @Doons So far as adding an `X`, you need to first understand how regular expressions are constructed. See [How to use Regular Expressions (Regex) in Microsoft Excel](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). There is no provision for a regex pattern being a VBA array. If you wanted to remove `S` **or** `X`, you would change `Const sPat as String = "S|X"` – Ron Rosenfeld Jan 10 '21 at 19:12