Len & Substitute

Question

I just struggling out with some VBA code.

Range("N1").Select
ActiveCell.Value = "One or more emails adresses?"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=len(RC[-7])-len(substitute(RC[-7];""@"";""""))"

I just want insert to cell N2 formula: =LEN(G2)-LEN(SUBSTITUTE(G2;"@";""))

Where I had made a mistake? Thanks for any help

you are separating the arguments of substitute with semicolons. You need commas — Super Symmetry, Jul 08 '20 at 11:20

Super Symmetry · Accepted Answer · 2020-07-08T12:16:42.317

2

Try

ActiveCell.Formula = "=LEN(G2)-LEN(SUBSTITUTE(G2,""@"",""""))"

Edit: following comments

You can use the following to avoid selecting cells

Range("N1").Value = "One or more emails adresses?"
Range("N2").Formula = "=LEN(G2)-LEN(SUBSTITUTE(G2,""@"",""""))"

edited Jul 08 '20 at 12:16

answered Jul 08 '20 at 11:22

Super Symmetry

2,837
1
6
17

1

Or `Range("N2").Formula.....`. `Range("N2").Select` is just not needed. [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Darren Bartrup-Cook Jul 08 '20 at 11:55
Thanks @DarrenBartrup-Cook, I think I need to be a little nicer :) – Super Symmetry Jul 08 '20 at 12:17

Len & Substitute

1 Answers1