Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why am i getting an error 1004?? please help | Excel Discussion (Misc queries) | |||
error 1004 (VBA) | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |