#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 468
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table for Dummies kjmcewan Excel Discussion (Misc queries) 1 April 2nd 07 09:24 PM
Provide a phone # to help the dummies. sibby sisti New Users to Excel 1 December 8th 05 07:16 PM
Conditional Formatting for Dummies PW11111 Excel Discussion (Misc queries) 2 October 10th 05 03:57 PM
DGET for dummies Lost on DGET New Users to Excel 1 September 11th 05 02:11 AM
protection, formulas and sharing with dummies Rob Excel Discussion (Misc queries) 1 July 27th 05 09:26 AM


All times are GMT +1. The time now is 09:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"