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

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

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

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

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

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

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

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

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

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