Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
I just recorded my first macros and was thrilled to have them work
properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
post your existing code for starters, so that specifics can be addressed.
"Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
Here's what the macro looks like:
Sub DailyBathsTotals() ' ' DailyBathsTotals Macro ' ' Keyboard Shortcut: Ctrl+s ' ActiveCell.FormulaR1C1 = "=(COUNTIF(R[-171]C:R[-4]C,""=*s*""))" Range("B174").Select End Sub -- Valerie "JMay" wrote: post your existing code for starters, so that specifics can be addressed. "Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
Maybe just using the 2nd row of that column would be sufficient:
Sub DailyBathsTotals() ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" End Sub r2c means 2nd row, same column r[-4]c means 4 rows up, same column Valerie wrote: Here's what the macro looks like: Sub DailyBathsTotals() ' ' DailyBathsTotals Macro ' ' Keyboard Shortcut: Ctrl+s ' ActiveCell.FormulaR1C1 = "=(COUNTIF(R[-171]C:R[-4]C,""=*s*""))" Range("B174").Select End Sub -- Valerie "JMay" wrote: post your existing code for starters, so that specifics can be addressed. "Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
I still get the same result.
-- Valerie "Dave Peterson" wrote: Maybe just using the 2nd row of that column would be sufficient: Sub DailyBathsTotals() ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" End Sub r2c means 2nd row, same column r[-4]c means 4 rows up, same column Valerie wrote: Here's what the macro looks like: Sub DailyBathsTotals() ' ' DailyBathsTotals Macro ' ' Keyboard Shortcut: Ctrl+s ' ActiveCell.FormulaR1C1 = "=(COUNTIF(R[-171]C:R[-4]C,""=*s*""))" Range("B174").Select End Sub -- Valerie "JMay" wrote: post your existing code for starters, so that specifics can be addressed. "Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
You said you were missing the 1st few rows. could it be that your row number
is going negative because you are moving up 4 rows. This formula will only work from row 5 and up. "Valerie" wrote: I still get the same result. -- Valerie "Dave Peterson" wrote: Maybe just using the 2nd row of that column would be sufficient: Sub DailyBathsTotals() ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" End Sub r2c means 2nd row, same column r[-4]c means 4 rows up, same column Valerie wrote: Here's what the macro looks like: Sub DailyBathsTotals() ' ' DailyBathsTotals Macro ' ' Keyboard Shortcut: Ctrl+s ' ActiveCell.FormulaR1C1 = "=(COUNTIF(R[-171]C:R[-4]C,""=*s*""))" Range("B174").Select End Sub -- Valerie "JMay" wrote: post your existing code for starters, so that specifics can be addressed. "Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
I'm not sure what this means.
Your formula depends on the cell that you're in. It will always include the second row of that column through the cell that's 4 rows up from the activecell. If you want to always use row 2 through row 169, you could use: ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R169C,""=*s*"")" You may want to explain where the activecell is and what rows you want included--either as a relative position (4 rows up) or an absolute row (always row 2 to row 169). Valerie wrote: I still get the same result. -- Valerie "Dave Peterson" wrote: Maybe just using the 2nd row of that column would be sufficient: Sub DailyBathsTotals() ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" End Sub r2c means 2nd row, same column r[-4]c means 4 rows up, same column Valerie wrote: Here's what the macro looks like: Sub DailyBathsTotals() ' ' DailyBathsTotals Macro ' ' Keyboard Shortcut: Ctrl+s ' ActiveCell.FormulaR1C1 = "=(COUNTIF(R[-171]C:R[-4]C,""=*s*""))" Range("B174").Select End Sub -- Valerie "JMay" wrote: post your existing code for starters, so that specifics can be addressed. "Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
Hi Dave,
The active cell is a few rows below the last entry. In this case, the last entry is on row 158, and the active cell is B178. I want to total all occurrences of "S" in cells B2 through B158. However, I may add 3 more rows tomorrow, so I was trying to build in some room for expansion by making the last row in the formula 169. Ideally, I want to use row 2 through whatever the last row is that contains data. A new column will be used each day, and I want to run a macro to do the totals. Does this make any sense? I tried the formulas you and Joel gave me, to no avail. -- Valerie "Dave Peterson" wrote: I'm not sure what this means. Your formula depends on the cell that you're in. It will always include the second row of that column through the cell that's 4 rows up from the activecell. If you want to always use row 2 through row 169, you could use: ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R169C,""=*s*"")" You may want to explain where the activecell is and what rows you want included--either as a relative position (4 rows up) or an absolute row (always row 2 to row 169). Valerie wrote: I still get the same result. -- Valerie "Dave Peterson" wrote: Maybe just using the 2nd row of that column would be sufficient: Sub DailyBathsTotals() ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" End Sub r2c means 2nd row, same column r[-4]c means 4 rows up, same column Valerie wrote: Here's what the macro looks like: Sub DailyBathsTotals() ' ' DailyBathsTotals Macro ' ' Keyboard Shortcut: Ctrl+s ' ActiveCell.FormulaR1C1 = "=(COUNTIF(R[-171]C:R[-4]C,""=*s*""))" Range("B174").Select End Sub -- Valerie "JMay" wrote: post your existing code for starters, so that specifics can be addressed. "Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
I don't understand how you could be missing rows at the top of the range.
Bot this formula: ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R169C,""=*s*"")" and this formula ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" Both start at row 2 of that column. The top version stops at row 169. The bottom formula stops 4 rows above where the active cell is. Maybe it's not the formula that's the problem. Maybe it's where the activecell is????? Valerie wrote: Hi Dave, The active cell is a few rows below the last entry. In this case, the last entry is on row 158, and the active cell is B178. I want to total all occurrences of "S" in cells B2 through B158. However, I may add 3 more rows tomorrow, so I was trying to build in some room for expansion by making the last row in the formula 169. Ideally, I want to use row 2 through whatever the last row is that contains data. A new column will be used each day, and I want to run a macro to do the totals. Does this make any sense? I tried the formulas you and Joel gave me, to no avail. -- Valerie "Dave Peterson" wrote: I'm not sure what this means. Your formula depends on the cell that you're in. It will always include the second row of that column through the cell that's 4 rows up from the activecell. If you want to always use row 2 through row 169, you could use: ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R169C,""=*s*"")" You may want to explain where the activecell is and what rows you want included--either as a relative position (4 rows up) or an absolute row (always row 2 to row 169). Valerie wrote: I still get the same result. -- Valerie "Dave Peterson" wrote: Maybe just using the 2nd row of that column would be sufficient: Sub DailyBathsTotals() ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" End Sub r2c means 2nd row, same column r[-4]c means 4 rows up, same column Valerie wrote: Here's what the macro looks like: Sub DailyBathsTotals() ' ' DailyBathsTotals Macro ' ' Keyboard Shortcut: Ctrl+s ' ActiveCell.FormulaR1C1 = "=(COUNTIF(R[-171]C:R[-4]C,""=*s*""))" Range("B174").Select End Sub -- Valerie "JMay" wrote: post your existing code for starters, so that specifics can be addressed. "Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Dummies
Apparently that was the problem. I deleted the previous macros and started
over, in a slightly different place (just a few cells up). It worked perfectly. Thanks so much for all your help. -- Valerie "Dave Peterson" wrote: I don't understand how you could be missing rows at the top of the range. Bot this formula: ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R169C,""=*s*"")" and this formula ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" Both start at row 2 of that column. The top version stops at row 169. The bottom formula stops 4 rows above where the active cell is. Maybe it's not the formula that's the problem. Maybe it's where the activecell is????? Valerie wrote: Hi Dave, The active cell is a few rows below the last entry. In this case, the last entry is on row 158, and the active cell is B178. I want to total all occurrences of "S" in cells B2 through B158. However, I may add 3 more rows tomorrow, so I was trying to build in some room for expansion by making the last row in the formula 169. Ideally, I want to use row 2 through whatever the last row is that contains data. A new column will be used each day, and I want to run a macro to do the totals. Does this make any sense? I tried the formulas you and Joel gave me, to no avail. -- Valerie "Dave Peterson" wrote: I'm not sure what this means. Your formula depends on the cell that you're in. It will always include the second row of that column through the cell that's 4 rows up from the activecell. If you want to always use row 2 through row 169, you could use: ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R169C,""=*s*"")" You may want to explain where the activecell is and what rows you want included--either as a relative position (4 rows up) or an absolute row (always row 2 to row 169). Valerie wrote: I still get the same result. -- Valerie "Dave Peterson" wrote: Maybe just using the 2nd row of that column would be sufficient: Sub DailyBathsTotals() ActiveCell.FormulaR1C1 = "=COUNTIF(R2C:R[-4]C,""=*s*"")" End Sub r2c means 2nd row, same column r[-4]c means 4 rows up, same column Valerie wrote: Here's what the macro looks like: Sub DailyBathsTotals() ' ' DailyBathsTotals Macro ' ' Keyboard Shortcut: Ctrl+s ' ActiveCell.FormulaR1C1 = "=(COUNTIF(R[-171]C:R[-4]C,""=*s*""))" Range("B174").Select End Sub -- Valerie "JMay" wrote: post your existing code for starters, so that specifics can be addressed. "Valerie" wrote: I just recorded my first macros and was thrilled to have them work properly...up to a point. The cell range in my macro is B2:B169. It did exactly what I wanted it to -- totalled the occurrences of "N", "S", "X", etc. in column B. However, when I ran the same macro in column C, it did not include the first few rows, so the totals were incorrect. I want to be able to run the same macro in column C, then column D, etc., including cells C2:169, D2:169, etc. I've read through some of the previous questions regarding macros, but to be honest, it's all over my head. I'm hoping someone can provide me with a very SIMPLE instruction on how to fix this. -- Valerie -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table for Dummies | Excel Discussion (Misc queries) | |||
Provide a phone # to help the dummies. | New Users to Excel | |||
Conditional Formatting for Dummies | Excel Discussion (Misc queries) | |||
DGET for dummies | New Users to Excel | |||
protection, formulas and sharing with dummies | Excel Discussion (Misc queries) |