1

I came pretty close to solving my problem with this answer given by "barry houdini" But I run into problems incorporating it to my needs.

 =SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"*","")))

Edit #3 - @Rory came with a very good answer to solving my problem. But what I hadn't thought about was if you enter a non-numerical value inside a cell, then the formula returns an error. So I would really like to tweak the following formula to ignore the letter "x", or non-numerical values (if that's easier):

-

ActiveCell.FormulaR1C1 = "=SUMPRODUCT(VALUE(0&SUBSTITUTE(INDIRECT(""R10C:R[-1]C"",FALSE),""s"","""")))" Dim Cell As Range For Each Cell In Range("all_cells") Cell.Formula = Range("one_cell").Formula Next

What I'm now trying to do, is write a VBA script to summarize a dynamic range, AND include values that have an "S" behind them BUT not fail or return an error on any other letters. Most importantly the letter "x".

After that first formula is set, I want to autofill that formula to the right ; stopping on a known column.

End edit

For the sake of history and other google searches, here's the original code I used before having the "s" and "x" problem. 

ActiveCell.FormulaR1C1 = _
    "=SUM(INDIRECT(ADDRESS(10,COLUMN())&"":""&ADDRESS(ROW()-1,COLUMN())))"

What this does is basically summarize from a set row (row#10) and stop the summary on the row just before the cell with this formula.

Example of what I want to achieve: "3+4S+2 + x = 9" ; instead of "= 5" or "##"

Community
  • 1
  • 1
Doons
  • 183
  • 11
  • If entering an array formula (i.e. it requires Ctrl+Shift+Enter) in VBA, you need the `FormulaArray` property. – Rory Jul 27 '15 at 15:53
  • I tried putting this into VBA: `ActiveCell.FormulaArray = _ "=SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"s","")))"` But I still got the same Compile error. – Doons Jul 27 '15 at 16:29
  • I'm going places!! I found this: "Double-quotes in strings need to be escaped by doubling them up:" So I have now made myself able to insert that code snippet into VBA. Now the testing begins. – Doons Jul 27 '15 at 16:37
  • Edited my question a little. Not sure if I exceed the 255 characters, but I am not bypassing the array-range-class error as easy as I hoped too... – Doons Jul 27 '15 at 17:34
  • Hi @Doons, do I understand correctly that on every attempt you have made to enter the formula via vba, you have done so over an entire range? If I remember correctly, I was unable to enter formulas over an entire range and had to instead use a `For Each (some range variable name, like Cell1) in (your range variable)` loop. Please let me know if I either misunderstood or this doesn't work for you. – puzzlepiece87 Jul 27 '15 at 18:10
  • Yes and no, I am not so sturdy in how the named ranges work, but I named a cell "cell_one", and reference to this cell in my formula. After one cell gets this formula, I run the command: Range("all_cells").Formula = Range("cell_one").Formula ; to autofill the formula to the cells I want. So I believe I have just attempted only one cell with this formula, but I am not sure since I use the "range command" and have a named reference – Doons Jul 27 '15 at 19:23
  • How many cells are in the named range "all_cells"? You probably need to loop like @puzzlepiece87 was saying: e.g. `Dim Cell as Range` `For Each Cell In Range("all_cells")` `Cell.Formula=Range("cell_one").Formula` `Next` – CBRF23 Jul 28 '15 at 02:49
  • What happens if you use `ActiveCell.FormulaR1C1 = =SUMPRODUCT(VALUE(0&SUBSTITUTE(INDIRECT(""R10C:R[-1]C"",FALSE),""s"","""")))` – Rory Jul 28 '15 at 09:49
  • @Rory : What happens is that it works like a charm! :D Thanks alot! – Doons Jul 28 '15 at 10:08
  • Ehrm...What I now see, which wasn't a problem before, is that the letter "x" should be ignored... So when entering "x" inside a cell, this should not create an error in the formula. Is this possible? I'm sorry for not understanding that this would be a problem earlier... – Doons Jul 28 '15 at 10:17

1 Answers1

0

I finally solved my problem! I realized nesting the substitute formulas could be a nifty solution, and after several errors I realized that the range only needed to be mentioned once, and voila!

ActiveCell.FormulaR1C1 = "=SUMPRODUCT(VALUE(0&SUBSTITUTE(0&SUBSTITUTE(INDIRECT(""R10C:R[-1]C"",FALSE),""s"",""""),""x"","""")))"

Doons
  • 183
  • 11