RT- error 1004
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, |
RT- error 1004
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, |
RT- error 1004
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, |
RT- error 1004
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, |
All times are GMT +1. The time now is 02:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com