CODEWITHSUNDEEP
excelvbaloopsexcel-formula
Shin
Shin

Reputation: 33

Inputting a formula over a column

I have this formula =1+ LEN(AT2) - LEN(SUBSTITUTE(AT2, "+", "")) that I want to apply over a column AU, that dynamically changes the variable (AT2 in this case) as iterates to the next row e.g. AT2, AT3, AT4.

Currently, this code keeps getting thrown an

application/object error

and I can't seem to get around it. Essentially what I want the formula to do is count the number of "+" that occur in the column before and add 1 to that number. Any help would be great! Thanks in advance.

With wsIt
    Range("AU2:AU" & LastRow).Formula = "=LEN(AT2) - LEN(SUBSTITUTE(AT2, " + ", ""))"
End With

Upvotes: 1

Views: 57

Answers (4)

virtualdvid
virtualdvid

Reputation: 2421

If you want a dynamic formula for each cell AT2, AT3, AT4... use FormulaR1C1, count the criteria "+" without spaces, and double Quotation marks ""+"" """":

.Range("AU2:AU" & LastRow).FormulaR1C1 = "=LEN(RC[-1]) - LEN(SUBSTITUTE(RC[-1], ""+"", """"))+1"

Upvotes: 1

AJD
AJD

Reputation: 2438

Missing a "." in front of Range - should be:

With wsIt
    .Range("AU2:AU" & LastRow).Formula = "=LEN(AT2) - LEN(SUBSTITUTE(AT2, " + ", ""))"
End With

The other answers regarding """" also apply - could use chr(34) to place a quote as well.

Upvotes: 0

user4039065
user4039065

Reputation:

As mentioned in other responses, you need to double-up double quotes within a quoted string. Alternately you can use methods that reproduce the same results without requiring quotes.

With wsIt
    Range("AU2:AU" & LastRow).Formula = "=LEN(AT2) - LEN(SUBSTITUTE(AT2, CHAR(41), TEXT(,)))"
End With

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23285

I believe it's just that you just need to double up on the quotes, when using them inside the formula:

Range("AU2:AU" & LastRow).Formula = "=LEN(AT2) - LEN(SUBSTITUTE(AT2, "" + "", """"))"

Note: Be aware that this is searching for [space]+[space] to substitute, so it won't substitute a blank for asd+asd, just asd + asd)

enter image description here

Upvotes: 2

Related Questions