Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Why does the macro not work?
Duke Carey wrote:
You ought to receive input from some of the sharper advisors here, but for the meantime I'd suggest you use a custom function like the one below Function Count_X(rng As Range, strTest As String) As Integer Dim cc As Range Count_X = 0 For Each cc In rng If cc.Value = strTest Then Count_X = Count_X + 1 Next End Function After pasting it into a module, you'd use it by entering this formula in a cell =count_x((K6,R6,Y6,AF6,AM6,AT6,BA6,BH6,BO6,BV6,CC6 ,CJ6,CQ6,CX6,DE6,DL6,DS6,DZ6,EG6,EN6),"X") I did this while the rest of my spreadsheet was still imcomplete. It worked on 1 page, but not the other 16 that I needed it used on. I decided to delete it temproarily until the rest of the sheets are completed. They are now done, and after putting the macro back in, it's not doing anything. What am I doing wrong? I've never used a macro before, and like most help sections... it's written in Greek. |
#2
|
|||
|
|||
How about a formula instead as opposed to a UDF, that will take a range as
opposed to multiple arguments:- =SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=4),--(K6:EN6="X")) This assumes that you don't vary the columns in anyway though, ie not adding or deleting columns, including any before the range starts in Cols A:J. If that may happen, then you could always use:- =SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=MOD(COLUMN(K6),7)),--(K6:EN6="X")) but this still assumes no variation of columns inside your range, ie there are currently 6 columns between each possible X. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Xanadude" wrote in message ... Duke Carey wrote: You ought to receive input from some of the sharper advisors here, but for the meantime I'd suggest you use a custom function like the one below Function Count_X(rng As Range, strTest As String) As Integer Dim cc As Range Count_X = 0 For Each cc In rng If cc.Value = strTest Then Count_X = Count_X + 1 Next End Function After pasting it into a module, you'd use it by entering this formula in a cell =count_x((K6,R6,Y6,AF6,AM6,AT6,BA6,BH6,BO6,BV6,CC6 ,CJ6,CQ6,CX6,DE6,DL6,DS6,D Z6,EG6,EN6),"X") I did this while the rest of my spreadsheet was still imcomplete. It worked on 1 page, but not the other 16 that I needed it used on. I decided to delete it temproarily until the rest of the sheets are completed. They are now done, and after putting the macro back in, it's not doing anything. What am I doing wrong? I've never used a macro before, and like most help sections... it's written in Greek. |
#3
|
|||
|
|||
My bad...
Forgot to include: I can't use the range (K6:EN6) because of a similar =COUNTIF function that runs on the same row. "Ken Wright" wrote: How about a formula instead as opposed to a UDF, that will take a range as opposed to multiple arguments:- =SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=4),--(K6:EN6="X")) This assumes that you don't vary the columns in anyway though, ie not adding or deleting columns, including any before the range starts in Cols A:J. If that may happen, then you could always use:- =SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=MOD(COLUMN(K6),7)),--(K6:EN6="X")) but this still assumes no variation of columns inside your range, ie there are currently 6 columns between each possible X. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Xanadude" wrote in message ... Duke Carey wrote: You ought to receive input from some of the sharper advisors here, but for the meantime I'd suggest you use a custom function like the one below Function Count_X(rng As Range, strTest As String) As Integer Dim cc As Range Count_X = 0 For Each cc In rng If cc.Value = strTest Then Count_X = Count_X + 1 Next End Function After pasting it into a module, you'd use it by entering this formula in a cell =count_x((K6,R6,Y6,AF6,AM6,AT6,BA6,BH6,BO6,BV6,CC6 ,CJ6,CQ6,CX6,DE6,DL6,DS6,D Z6,EG6,EN6),"X") I did this while the rest of my spreadsheet was still imcomplete. It worked on 1 page, but not the other 16 that I needed it used on. I decided to delete it temproarily until the rest of the sheets are completed. They are now done, and after putting the macro back in, it's not doing anything. What am I doing wrong? I've never used a macro before, and like most help sections... it's written in Greek. |
#4
|
|||
|
|||
Maybe it's just a text comparison difference.
Unless you do something special, X < x in VBA. You could add: Option Compare Text at the top of the module or you could do something like: If lcase(cc.Value) = lcase(strTest) Then Count_X = Count_X + 1 or even: If StrComp(cc.Value, strTest, vbTextCompare) = 0 Then Count_X = Count_X + 1 Xanadude wrote: Duke Carey wrote: You ought to receive input from some of the sharper advisors here, but for the meantime I'd suggest you use a custom function like the one below Function Count_X(rng As Range, strTest As String) As Integer Dim cc As Range Count_X = 0 For Each cc In rng If cc.Value = strTest Then Count_X = Count_X + 1 Next End Function After pasting it into a module, you'd use it by entering this formula in a cell =count_x((K6,R6,Y6,AF6,AM6,AT6,BA6,BH6,BO6,BV6,CC6 ,CJ6,CQ6,CX6,DE6,DL6,DS6,DZ6,EG6,EN6),"X") I did this while the rest of my spreadsheet was still imcomplete. It worked on 1 page, but not the other 16 that I needed it used on. I decided to delete it temproarily until the rest of the sheets are completed. They are now done, and after putting the macro back in, it's not doing anything. What am I doing wrong? I've never used a macro before, and like most help sections... it's written in Greek. -- Dave Peterson |
#5
|
|||
|
|||
I may be missing something here but can't see why that matters. The macro
runs through each cell in the selection of cells you put in to the function, which from the look of it is every 7th column. It then checks each cell to see if it has an X in it, and if so it counts it. The formula does the same, and doesn't matter what else is in between or also querying the same range? You can put the formula in any column outside your range so that shouldn't be a limitation. I was assuming you would simply put the formula wherever you had your function. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Xanadude" wrote in message ... My bad... Forgot to include: I can't use the range (K6:EN6) because of a similar =COUNTIF function that runs on the same row. "Ken Wright" wrote: How about a formula instead as opposed to a UDF, that will take a range as opposed to multiple arguments:- =SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=4),--(K6:EN6="X")) This assumes that you don't vary the columns in anyway though, ie not adding or deleting columns, including any before the range starts in Cols A:J. If that may happen, then you could always use:- =SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=MOD(COLUMN(K6),7)),--(K6:EN6="X")) but this still assumes no variation of columns inside your range, ie there are currently 6 columns between each possible X. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Xanadude" wrote in message ... Duke Carey wrote: You ought to receive input from some of the sharper advisors here, but for the meantime I'd suggest you use a custom function like the one below Function Count_X(rng As Range, strTest As String) As Integer Dim cc As Range Count_X = 0 For Each cc In rng If cc.Value = strTest Then Count_X = Count_X + 1 Next End Function After pasting it into a module, you'd use it by entering this formula in a cell =count_x((K6,R6,Y6,AF6,AM6,AT6,BA6,BH6,BO6,BV6,CC6 ,CJ6,CQ6,CX6,DE6,DL6,DS6,D Z6,EG6,EN6),"X") I did this while the rest of my spreadsheet was still imcomplete. It worked on 1 page, but not the other 16 that I needed it used on. I decided to delete it temproarily until the rest of the sheets are completed. They are now done, and after putting the macro back in, it's not doing anything. What am I doing wrong? I've never used a macro before, and like most help sections... it's written in Greek. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a macro be made to work based on a formula? | Excel Discussion (Misc queries) | |||
How do I record a macro which should work on multiple files ? | Excel Discussion (Misc queries) | |||
Assign Macro to button in Excel doesnt work Any ideas? | Excel Discussion (Misc queries) | |||
Record Macro Relative does not work? | Excel Discussion (Misc queries) | |||
Record Macro Relative does not work? | Excel Discussion (Misc queries) |