Execute macro many times??

[mtnbikr9]

My extent of knowledge with VBA is pretty much plug-and-play, so hopefully this should be pretty easy...

I have a spreadsheet with 28+ sheets/plys.

I have a simple macro that I need to execute many times on many of these sheets:
________________
Sub FindRoot()
'
' FindRoot Macro
' Macro recorded 4/1/02
'
' Keyboard Shortcut: Ctrl+Shift+R
'
ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Cells(1, -2)
ActiveCell.Cells(2, 1).Select
End Sub
________________

Right now it is laborious to go to each sheet, select the right cells and execute the macro for all those cells. Below are examples of the general cases I would like to execute:

I need to execute this macro on sheet 'Order 3' starting at cell I11 and continuing down the column for a total number of iterations equal to the number of user inputted values, i.e. user inputs 70 values in Column A, macro will execute 70 times starting at cell I11.

I need to execute this macro on sheet 'Order 3' starting at cell W11 and continuing down the column exactly 150 times to cell W160.

I need to execute this macro on sheet 'Lookup' starting at cell I13 and continuing down the column for all rows where a numerical value exists in the corresponding B column.

I also need to execute this macro on sheet 'Compare' starting at cell F11 and continuing until the text string "Max Exceeded" appears in the corrresponding cell in Column E.

I would like to link macro execution to the push of a control button inserted on the main sheet of the spreadsheet (push once, all values are calculated). A key combination would also work.

If someone has some guidance on fulfilling the general cases above, I should be able to alter the program to fulfill all of my needs (which would be awesome!)

[excelent]

Sub MakeMyDay()

Dim r, rloop

Sheets("Order 3").Activate
r = Cells(65500, 1).End(xlUp).Row - 10
Cells(11, 9).Activate
For rloop = 1 To r
Call FindRoot
ActiveCell.Offset(1, 0).Activate
Next


'...
'working on it
'...

End Sub

[Ed]

Do you always start in the same places - cells I11 and W11 on Order3, I13 on
Lookup, and F11 on Compare? Do you always go exactly 150 cells from W11?
Can you determine the number of user inputted values on Order3?

The more constants you have, the easier to automate. Another way,
especially if things keep changing on you, would be to select your starting
cell, find and Shift-click your ending cell (this creates a continuous
selection), and then run something like this:

Sub X_X_Test()

Dim myRange As Range
Dim myCell As Range

Set myRange = Selection
For Each myCell In myRange.Cells
myCell.GoalSeek Goal:=0, ChangingCell:=myCell.Cells(1, -2)
Next myCell

End Sub

HTH
Ed

"mtnbikr9" <[email protected]> wrote in
message news:[email protected]...
>
> My extent of knowledge with VBA is pretty much plug-and-play, so
> hopefully this should be pretty easy...
>
> I have a spreadsheet with 28+ sheets/plys.
>
> I have a simple macro that I need to execute many times on many of
> these sheets:
> ________________
> Sub FindRoot()
> '
> ' FindRoot Macro
> ' Macro recorded 4/1/02
> '
> ' Keyboard Shortcut: Ctrl+Shift+R
> '
> ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Cells(1, -2)
> ActiveCell.Cells(2, 1).Select
> End Sub
> ________________
>
> Right now it is laborious to go to each sheet, select the right cells
> and execute the macro for all those cells. Below are examples of the
> general cases I would like to execute:
>
> I need to execute this macro on sheet 'Order 3' starting at cell I11
> and continuing down the column for a total number of iterations equal
> to the number of user inputted values, i.e. user inputs 70 values in
> Column A, macro will execute 70 times starting at cell I11.
>
> I need to execute this macro on sheet 'Order 3' starting at cell W11
> and continuing down the column exactly 150 times to cell W160.
>
> I need to execute this macro on sheet 'Lookup' starting at cell I13 and
> continuing down the column for all rows where a numerical value exists
> in the corresponding B column.
>
> I also need to execute this macro on sheet 'Compare' starting at cell
> F11 and continuing until the text string "Max Exceeded" appears in the
> corrresponding cell in Column E.
>
> I would like to link macro execution to the push of a control button
> inserted on the main sheet of the spreadsheet (push once, all values
> are calculated). A key combination would also work.
>
> If someone has some guidance on fulfilling the general cases above, I
> should be able to alter the program to fulfill all of my needs (which
> would be awesome!)
>
>
> --
> mtnbikr9
> ------------------------------------------------------------------------
> mtnbikr9's Profile:
> http://www.excelforum.com/member.php...o&userid=35429
> View this thread: http://www.excelforum.com/showthread...hreadid=551992
>

[excelent]

Sub MakeMyDay()

Dim r, rloop

Sheets("Order 3").Activate
r = Cells(65500, 1).End(xlUp).Row - 10
Cells(11, 9).Activate
For rloop = 1 To r
Call FindRoot
ActiveCell.Offset(1, 0).Activate
Next

For Each r In Range("W11:w160")
Call FindRoot
Next

Sheets("Lookup").Activate
r = Cells(65500, 9).End(xlUp).Row - 12
Cells(13, 9).Activate
For rloop = 1 To r
If IsNumeric(Cells(rloop, 2)) Then
Call FindRoot
Cells(rloop, 9).Activate
Next

Sheets("Compare").Activate
Cells(11, 6).Activate
xloop:
If Cells(11 + x, 5) <> "Max Exceeded" Then
Call FindRoot
x = x + 1
GoTo xloop
End If


End Sub

[mtnbikr9]

Thank you!!

I have grabbed parts of your code and combined it with code I had already started making some progress on.

The IsNumeric function wasn't working for me. I think it was because the values in the cells I was referring to were a result of an IF statement (not direct user input values as I indicated), not sure. I used IsError instead and applied it to the ActiveCell since I didnt need to do FindRoot when a VALUE error was present.

Works great!