Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Configure macro to run on all sheets Excel 2k7

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 236
Default Configure macro to run on all sheets Excel 2k7

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,203
Default Configure macro to run on all sheets Excel 2k7

That will work, but (in my opinion) bad form to try to use reserved words as
variables or constants. But I think I know why you did it in this case.

Probably better for OP to do it this way:

Sub CalcDates()
Dim anySheet as Worksheet
For Each anySheet in ThisWorkbook.Worksheets
'.... the other code here
Next ' end of anySheet loop
End Sub

"Gary Brown" wrote:

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 236
Default Configure macro to run on all sheets Excel 2k7

I totally agree but was working with what he had.
Thanks,
Gary

-



"JLatham" wrote:

That will work, but (in my opinion) bad form to try to use reserved words as
variables or constants. But I think I know why you did it in this case.

Probably better for OP to do it this way:

Sub CalcDates()
Dim anySheet as Worksheet
For Each anySheet in ThisWorkbook.Worksheets
'.... the other code here
Next ' end of anySheet loop
End Sub

"Gary Brown" wrote:

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Configure macro to run on all sheets Excel 2k7

With both solutions I still have the same result. The macro never moves on to
the next sheet.

"JLatham" wrote:

That will work, but (in my opinion) bad form to try to use reserved words as
variables or constants. But I think I know why you did it in this case.

Probably better for OP to do it this way:

Sub CalcDates()
Dim anySheet as Worksheet
For Each anySheet in ThisWorkbook.Worksheets
'.... the other code here
Next ' end of anySheet loop
End Sub

"Gary Brown" wrote:

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 236
Default Configure macro to run on all sheets Excel 2k7

Forgot to go to the next worksheet :O

Sub CalcDates()
Dim wks As Worksheet
For Each wks In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
wks.Activate
Next wks
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

With both solutions I still have the same result. The macro never moves on to
the next sheet.

"JLatham" wrote:

That will work, but (in my opinion) bad form to try to use reserved words as
variables or constants. But I think I know why you did it in this case.

Probably better for OP to do it this way:

Sub CalcDates()
Dim anySheet as Worksheet
For Each anySheet in ThisWorkbook.Worksheets
'.... the other code here
Next ' end of anySheet loop
End Sub

"Gary Brown" wrote:

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default Configure macro to run on all sheets Excel 2k7

The reason you continually loop thru the first sheet is that you never leave
it - you need a worksheets(nextsheetname).activate statement before the
cells(2,7).select

Get the above working then have a think about changing cell contents
without activating them - ie Cells(2,7).offset(0,-1).value = "Value" - Much
quicker, less screen flash

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Gary Brown" wrote:

Forgot to go to the next worksheet :O

Sub CalcDates()
Dim wks As Worksheet
For Each wks In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
wks.Activate
Next wks
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

With both solutions I still have the same result. The macro never moves on to
the next sheet.

"JLatham" wrote:

That will work, but (in my opinion) bad form to try to use reserved words as
variables or constants. But I think I know why you did it in this case.

Probably better for OP to do it this way:

Sub CalcDates()
Dim anySheet as Worksheet
For Each anySheet in ThisWorkbook.Worksheets
'.... the other code here
Next ' end of anySheet loop
End Sub

"Gary Brown" wrote:

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Configure macro to run on all sheets Excel 2k7


That did it thanks!
"Gary Brown" wrote:

Forgot to go to the next worksheet :O

Sub CalcDates()
Dim wks As Worksheet
For Each wks In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
wks.Activate
Next wks
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

With both solutions I still have the same result. The macro never moves on to
the next sheet.

"JLatham" wrote:

That will work, but (in my opinion) bad form to try to use reserved words as
variables or constants. But I think I know why you did it in this case.

Probably better for OP to do it this way:

Sub CalcDates()
Dim anySheet as Worksheet
For Each anySheet in ThisWorkbook.Worksheets
'.... the other code here
Next ' end of anySheet loop
End Sub

"Gary Brown" wrote:

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Configure macro to run on all sheets Excel 2k7

You and Gary both got it. Thanks! I realize the code may be amateurish but it
is my first try. Thanks for the additional tips.

"K_Macd" wrote:

The reason you continually loop thru the first sheet is that you never leave
it - you need a worksheets(nextsheetname).activate statement before the
cells(2,7).select

Get the above working then have a think about changing cell contents
without activating them - ie Cells(2,7).offset(0,-1).value = "Value" - Much
quicker, less screen flash

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Gary Brown" wrote:

Forgot to go to the next worksheet :O

Sub CalcDates()
Dim wks As Worksheet
For Each wks In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
wks.Activate
Next wks
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

With both solutions I still have the same result. The macro never moves on to
the next sheet.

"JLatham" wrote:

That will work, but (in my opinion) bad form to try to use reserved words as
variables or constants. But I think I know why you did it in this case.

Probably better for OP to do it this way:

Sub CalcDates()
Dim anySheet as Worksheet
For Each anySheet in ThisWorkbook.Worksheets
'.... the other code here
Next ' end of anySheet loop
End Sub

"Gary Brown" wrote:

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,203
Default Configure macro to run on all sheets Excel 2k7

Sorry, didn't know you wanted to move to the other sheets. I wrote it to NOT
move on purpose.
One of the slowest actions Excel can do is to constantly update/refresh the
video display, so unless there's a need to go another sheet, I generally
don't do it. Plus some users find it annoying to see a bunch of sheets
flashing on the screen in front of them.

"kevlarmcc" wrote:

With both solutions I still have the same result. The macro never moves on to
the next sheet.

"JLatham" wrote:

That will work, but (in my opinion) bad form to try to use reserved words as
variables or constants. But I think I know why you did it in this case.

Probably better for OP to do it this way:

Sub CalcDates()
Dim anySheet as Worksheet
For Each anySheet in ThisWorkbook.Worksheets
'.... the other code here
Next ' end of anySheet loop
End Sub

"Gary Brown" wrote:

I'd say you probably need to declare the worksheet....

Sub CalcDates()
Dim worksheet as worksheet
'stuff
End Sub

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"kevlarmcc" wrote:

I have some simple code that selects a cell, enters a value, selects some
more cells and enters a formula. When i follow suggestions to configure the
code to run on all worksheets I cannot get it right. What it does is run the
code on the active sheet as many times as there are sheets. So it's looping
but not going on to the next sheet. Can someone tell me what I have wrong? I
have included the code that works (for the active sheet only) and the code i
think would work but won't.

Works:

Sub CalcDates()
ActiveSheet.Cells(2, 7).Select
Selection.Value = "Days"
ActiveSheet.Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Doesn't work:

Sub CalcDates()
For Each Worksheet In Worksheets
Cells(2, 7).Select
Selection.Value = "Days"
Cells(3, 7).Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-4],RC[-6])"
ActiveCell.Offset(1, 0).Select
Loop
Next Worksheet
End Sub

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
Why must I configure excel with every use tshahen Excel Discussion (Misc queries) 1 July 16th 09 05:15 PM
How can I configure EXCEL to do Ancova DaleB Excel Discussion (Misc queries) 3 December 27th 06 07:57 PM
How to configure Excel startup MS learner Excel Programming 1 January 27th 06 04:19 PM
Which Forumla and How do I configure in Excel? Ben Excel Discussion (Misc queries) 2 September 26th 05 11:34 PM
Can I configure Excel for not to respond on "F1" key press Murtaza Excel Discussion (Misc queries) 6 May 11th 05 01:00 PM


All times are GMT +1. The time now is 11:55 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"