Macros for Dummies

[Valerie]

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.

 

[JMay]

post your existing code for starters, so that specifics can be addressed.

 

[Valerie]

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

 

[Dave Peterson]

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

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

post your existing code for starters, so that specifics can be addressed.

 

[Valerie]

I still get the same result.
--
Valerie

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

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

post your existing code for starters, so that specifics can be addressed.

:

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.

 

[Joel]

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.

I still get the same result.
--
Valerie

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

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

:

post your existing code for starters, so that specifics can be addressed.

:

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.

 

[Dave Peterson]

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).

I still get the same result.
--
Valerie

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

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

:

post your existing code for starters, so that specifics can be addressed.

:

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]

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

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).

I still get the same result.
--
Valerie

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

:

post your existing code for starters, so that specifics can be addressed.

:

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.

 

[Dave Peterson]

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?????

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

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).

I still get the same result.
--
Valerie

:

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

:

post your existing code for starters, so that specifics can be addressed.

:

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]

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

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?????

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

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

:

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

:

post your existing code for starters, so that specifics can be addressed.

:

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.