Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why must I configure excel with every use | Excel Discussion (Misc queries) | |||
How can I configure EXCEL to do Ancova | Excel Discussion (Misc queries) | |||
How to configure Excel startup | Excel Programming | |||
Which Forumla and How do I configure in Excel? | Excel Discussion (Misc queries) | |||
Can I configure Excel for not to respond on "F1" key press | Excel Discussion (Misc queries) |