In cell A1 I have:
A,B,C,D,E
In Cell I have:
=LEN(A1)-(LEN(SUBSTITUTE(A1,",",""))-1)

which produces 5 << as I expect

I'm wanting to enter this formula via VBA (a button) as follows:

Sub FindUnique()
ActiveSheet.Range("C3").Formula =
"=LEN($A$1)-(LEN(SUBSTITUTE($A$1),"","",""""))-1)"
End Sub

but it doesn't work. What am I doing wrong here...???

TIA,

Try this

Sub FindUnique()
ActiveSheet.Range("C3").Formula = _
"=LEN(SUBSTITUTE(A1,"","",""""))"
End Sub

Mike

"JMay" wrote:

In cell A1 I have:
A,B,C,D,E
In Cell I have:
=LEN(A1)-(LEN(SUBSTITUTE(A1,",",""))-1)

which produces 5 << as I expect

I'm wanting to enter this formula via VBA (a button) as follows:

Sub FindUnique()
ActiveSheet.Range("C3").Formula =
"=LEN($A$1)-(LEN(SUBSTITUTE($A$1),"","",""""))-1)"
End Sub

but it doesn't work. What am I doing wrong here...???

TIA,

You had an extra closing parenthsis in the VBA code. I often assign a
formula to a string variable to help diagnosis problems. when yuo have
parenthesis inside a formula these are sometimes problems. I can use debug
techniques to look at FORM below to help find the problems.


Sub FindUnique()
form = "=LEN($A$1)-(LEN(SUBSTITUTE($A$1,"","",""""))-1)"
Range("C3").Formula = form

End Sub

"JMay" wrote:

In cell A1 I have:
A,B,C,D,E
In Cell I have:
=LEN(A1)-(LEN(SUBSTITUTE(A1,",",""))-1)

which produces 5 << as I expect

I'm wanting to enter this formula via VBA (a button) as follows:

Sub FindUnique()
ActiveSheet.Range("C3").Formula =
"=LEN($A$1)-(LEN(SUBSTITUTE($A$1),"","",""""))-1)"
End Sub

but it doesn't work. What am I doing wrong here...???

TIA,

Thanks guys... (not sure how this extra ")" got in there. Thanks Joel for
the suggestion regarding the variable assignment. I need to remember that
going forward.

Jim

"Joel" wrote:

You had an extra closing parenthsis in the VBA code. I often assign a
formula to a string variable to help diagnosis problems. when yuo have
parenthesis inside a formula these are sometimes problems. I can use debug
techniques to look at FORM below to help find the problems.


Sub FindUnique()
form = "=LEN($A$1)-(LEN(SUBSTITUTE($A$1,"","",""""))-1)"
Range("C3").Formula = form

End Sub

"JMay" wrote:

In cell A1 I have:
A,B,C,D,E
In Cell I have:
=LEN(A1)-(LEN(SUBSTITUTE(A1,",",""))-1)

which produces 5 << as I expect

I'm wanting to enter this formula via VBA (a button) as follows:

Sub FindUnique()
ActiveSheet.Range("C3").Formula =
"=LEN($A$1)-(LEN(SUBSTITUTE($A$1),"","",""""))-1)"
End Sub

but it doesn't work. What am I doing wrong here...???

TIA,