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

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

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" wrote in
message ...

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

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

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! :)


--
mtnbikr9
------------------------------------------------------------------------
mtnbikr9's Profile: http://www.excelforum.com/member.php...o&userid=35429
View this thread: http://www.excelforum.com/showthread...hreadid=551992