Microsoft Visual Basic: Compile error: Sum or Function not defined

Hello
I receive:
"Microsoft Visual Basic
Compile error:
Sum or Function not defined" after I have run a macro. I recorded the macro.
The macro code is:
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 24.03.2006 by Dmitry Kopnichev
'

'
Range("J16").Select
SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
ByChange:="$F$4:$I$12"
SolverSolve
End Sub
All recorded operations work by itself. How to make the macro run the
operations?

Solver is not recognised by the standard Excel object model, add a reference
to SOLVER in your VB Editor.

Goto VB Editor (Alt-F11) , select Tools->References then check the SOLVER
option.- the SOLVER add in must be installed for this to be visible.

--
Cheers
Nigel



"Dmitry" <[email protected]> wrote in message
news:%[email protected]...
> Hello
> I receive:
> "Microsoft Visual Basic
> Compile error:
> Sum or Function not defined" after I have run a macro. I recorded the
> macro.
> The macro code is:
> Sub Macro2()
> '
> ' Macro2 Macro
> ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> '
>
> '
> Range("J16").Select
> SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> ByChange:="$F$4:$I$12"
> SolverSolve
> End Sub
> All recorded operations work by itself. How to make the macro run the
> operations?
>
>

Thank you, Nigel!
When I record the macro I press the Enter button when Solver Results window
appears to close it before I click the Stop recording macro button, but the
macro does not close the window. How to make the macro close the window?
"Nigel" <[email protected]> сообщил/сообщила в новостях следующее:
news:[email protected]...
> Solver is not recognised by the standard Excel object model, add a
reference
> to SOLVER in your VB Editor.
>
> Goto VB Editor (Alt-F11) , select Tools->References then check the SOLVER
> option.- the SOLVER add in must be installed for this to be visible.
>
> --
> Cheers
> Nigel
>
>
>
> "Dmitry" <[email protected]> wrote in message
> news:%[email protected]...
> > Hello
> > I receive:
> > "Microsoft Visual Basic
> > Compile error:
> > Sum or Function not defined" after I have run a macro. I recorded the
> > macro.
> > The macro code is:
> > Sub Macro2()
> > '
> > ' Macro2 Macro
> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> > '
> >
> > '
> > Range("J16").Select
> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> > ByChange:="$F$4:$I$12"
> > SolverSolve
> > End Sub
> > All recorded operations work by itself. How to make the macro run the
> > operations?
> >
> >
>
>

Thanks Nigel!
How to repeat the macro for a contiguous range of sheets in a Workbook? I
call the range contiguous because the range sheets tabs are contiguous in
the Workbook.
How to make the macro close the Solver Results window?
"Nigel" <[email protected]> сообщил/сообщила в новостях следующее:
news:[email protected]...
> Solver is not recognised by the standard Excel object model, add a
reference
> to SOLVER in your VB Editor.
>
> Goto VB Editor (Alt-F11) , select Tools->References then check the SOLVER
> option.- the SOLVER add in must be installed for this to be visible.
>
> --
> Cheers
> Nigel
>
>
>
> "Dmitry" <[email protected]> wrote in message
> news:%[email protected]...
> > Hello
> > I receive:
> > "Microsoft Visual Basic
> > Compile error:
> > Sum or Function not defined" after I have run a macro. I recorded the
> > macro.
> > The macro code is:
> > Sub Macro2()
> > '
> > ' Macro2 Macro
> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> > '
> >
> > '
> > Range("J16").Select
> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> > ByChange:="$F$4:$I$12"
> > SolverSolve
> > End Sub
> > All recorded operations work by itself. How to make the macro run the
> > operations?
> >
> >
>
>

http://support.microsoft.com/default...b;en-us;843304
How to create Visual Basic macros by using Excel Solver in Excel 97

should help you do what you want.

--
Regards,
Tom Ogilvy


"Dmitry" wrote:

> Thanks Nigel!
> How to repeat the macro for a contiguous range of sheets in a Workbook? I
> call the range contiguous because the range sheets tabs are contiguous in
> the Workbook.
> How to make the macro close the Solver Results window?
> "Nigel" <[email protected]> ц⌠ц▐ц▐ц┌ц²ц┴ц▄/ц⌠ц▐ц▐ц┌ц²ц┴ц▄ц│ ц≈ ц▌ц▐ц≈ц▐ц⌠ц■ц▒ц┬ ц⌠ц▄ц┘ц└ц∙ц─ц²ц┘ц┘:
> news:[email protected]...
> > Solver is not recognised by the standard Excel object model, add a
> reference
> > to SOLVER in your VB Editor.
> >
> > Goto VB Editor (Alt-F11) , select Tools->References then check the SOLVER
> > option.- the SOLVER add in must be installed for this to be visible.
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "Dmitry" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Hello
> > > I receive:
> > > "Microsoft Visual Basic
> > > Compile error:
> > > Sum or Function not defined" after I have run a macro. I recorded the
> > > macro.
> > > The macro code is:
> > > Sub Macro2()
> > > '
> > > ' Macro2 Macro
> > > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> > > '
> > >
> > > '
> > > Range("J16").Select
> > > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> > > ByChange:="$F$4:$I$12"
> > > SolverSolve
> > > End Sub
> > > All recorded operations work by itself. How to make the macro run the
> > > operations?
> > >
> > >
> >
> >
>
>
>

Not sure, but see if there are any ideas here that can help.
If not, please post back. :>)

Sub Demo()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
Sht.Activate
SolverReset
SolverOk "J16", 1, , "F4:I12"
SolverSolve True
Next Sht
End Sub

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Dmitry" <[email protected]> wrote in message
news:%[email protected]...
> Thanks Nigel!
> How to repeat the macro for a contiguous range of sheets in a Workbook? I
> call the range contiguous because the range sheets tabs are contiguous in
> the Workbook.
> How to make the macro close the Solver Results window?
> "Nigel" <[email protected]> сообщил/сообщила в новостях
> следующее:
> news:[email protected]...
>> Solver is not recognised by the standard Excel object model, add a
> reference
>> to SOLVER in your VB Editor.
>>
>> Goto VB Editor (Alt-F11) , select Tools->References then check the SOLVER
>> option.- the SOLVER add in must be installed for this to be visible.
>>
>> --
>> Cheers
>> Nigel
>>
>>
>>
>> "Dmitry" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Hello
>> > I receive:
>> > "Microsoft Visual Basic
>> > Compile error:
>> > Sum or Function not defined" after I have run a macro. I recorded the
>> > macro.
>> > The macro code is:
>> > Sub Macro2()
>> > '
>> > ' Macro2 Macro
>> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
>> > '
>> > '
>> > Range("J16").Select
>> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
>> > ByChange:="$F$4:$I$12"
>> > SolverSolve
>> > End Sub
>> > All recorded operations work by itself. How to make the macro run the
>> > operations?

Thanks Tom Ogilvy!
"Tom Ogilvy" <[email protected]> сообщил/сообщила в
новостях следующее:
news:[email protected]...
> http://support.microsoft.com/default...b;en-us;843304
> How to create Visual Basic macros by using Excel Solver in Excel 97
>
> should help you do what you want.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Dmitry" wrote:
>
> > Thanks Nigel!
> > How to repeat the macro for a contiguous range of sheets in a Workbook?
I
> > call the range contiguous because the range sheets tabs are contiguous
in
> > the Workbook.
> > How to make the macro close the Solver Results window?
> > "Nigel" <[email protected]> OIIAYEI/OIIAYEIA ? II?IOONE
OIAAOAYAA:
> > news:[email protected]...
> > > Solver is not recognised by the standard Excel object model, add a
> > reference
> > > to SOLVER in your VB Editor.
> > >
> > > Goto VB Editor (Alt-F11) , select Tools->References then check the
SOLVER
> > > option.- the SOLVER add in must be installed for this to be visible.
> > >
> > > --
> > > Cheers
> > > Nigel
> > >
> > >
> > >
> > > "Dmitry" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > Hello
> > > > I receive:
> > > > "Microsoft Visual Basic
> > > > Compile error:
> > > > Sum or Function not defined" after I have run a macro. I recorded
the
> > > > macro.
> > > > The macro code is:
> > > > Sub Macro2()
> > > > '
> > > > ' Macro2 Macro
> > > > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> > > > '
> > > >
> > > > '
> > > > Range("J16").Select
> > > > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> > > > ByChange:="$F$4:$I$12"
> > > > SolverSolve
> > > > End Sub
> > > > All recorded operations work by itself. How to make the macro run
the
> > > > operations?
> > > >
> > > >
> > >
> > >
> >
> >
> >

Thanks Dana DeLouis.
Does your code repeats for all worksheets in a workbook? I wrote "How to
repeat the macro for a contiguous range of sheets (not all sheets) in a
Workbook?" How to repeat the macro for selected sheets in a Workbook?
"Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
следующее: news:[email protected]...
> Not sure, but see if there are any ideas here that can help.
> If not, please post back. :>)
>
> Sub Demo()
> Dim Sht As Worksheet
> For Each Sht In ActiveWorkbook.Worksheets
> Sht.Activate
> SolverReset
> SolverOk "J16", 1, , "F4:I12"
> SolverSolve True
> Next Sht
> End Sub
>
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Dmitry" <[email protected]> wrote in message
> news:%[email protected]...
> > Thanks Nigel!
> > How to repeat the macro for a contiguous range of sheets in a Workbook?
I
> > call the range contiguous because the range sheets tabs are contiguous
in
> > the Workbook.
> > How to make the macro close the Solver Results window?
> > "Nigel" <[email protected]> сообщил/сообщила в новостях
> > следующее:
> > news:[email protected]...
> >> Solver is not recognised by the standard Excel object model, add a
> > reference
> >> to SOLVER in your VB Editor.
> >>
> >> Goto VB Editor (Alt-F11) , select Tools->References then check the
SOLVER
> >> option.- the SOLVER add in must be installed for this to be visible.
> >>
> >> --
> >> Cheers
> >> Nigel
> >>
> >>
> >>
> >> "Dmitry" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Hello
> >> > I receive:
> >> > "Microsoft Visual Basic
> >> > Compile error:
> >> > Sum or Function not defined" after I have run a macro. I recorded the
> >> > macro.
> >> > The macro code is:
> >> > Sub Macro2()
> >> > '
> >> > ' Macro2 Macro
> >> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> >> > '
> >> > '
> >> > Range("J16").Select
> >> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> >> > ByChange:="$F$4:$I$12"
> >> > SolverSolve
> >> > End Sub
> >> > All recorded operations work by itself. How to make the macro run the
> >> > operations?
>
>

> How to repeat the macro for selected sheets in a Workbook?

Hi. That was for all sheets. Would any ideas here help for selected
sheets?

Sub Demo()
Dim sht
For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
sht.Activate
' Solver here...
Next
End Sub

Sub Demo2()
Dim sht
For Each sht In Array("Sheet1", "Sheet3", "Sheet5")
Worksheets(sht).Activate
' Solver here...
Next
End Sub

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Dmitry" <[email protected]> wrote in message
news:[email protected]...
> Thanks Dana DeLouis.
> Does your code repeats for all worksheets in a workbook? I wrote "How to
> repeat the macro for a contiguous range of sheets (not all sheets) in a
> Workbook?" How to repeat the macro for selected sheets in a Workbook?
> "Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
> следующее: news:[email protected]...
>> Not sure, but see if there are any ideas here that can help.
>> If not, please post back. :>)
>>
>> Sub Demo()
>> Dim Sht As Worksheet
>> For Each Sht In ActiveWorkbook.Worksheets
>> Sht.Activate
>> SolverReset
>> SolverOk "J16", 1, , "F4:I12"
>> SolverSolve True
>> Next Sht
>> End Sub
>>
>> --
>> HTH. :>)
>> Dana DeLouis
>> Windows XP, Office 2003
>>
>>
>> "Dmitry" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Thanks Nigel!
>> > How to repeat the macro for a contiguous range of sheets in a Workbook?
> I
>> > call the range contiguous because the range sheets tabs are contiguous
> in
>> > the Workbook.
>> > How to make the macro close the Solver Results window?
>> > "Nigel" <[email protected]> сообщил/сообщила в новостях
>> > следующее:
>> > news:[email protected]...
>> >> Solver is not recognised by the standard Excel object model, add a
>> > reference
>> >> to SOLVER in your VB Editor.
>> >>
>> >> Goto VB Editor (Alt-F11) , select Tools->References then check the
> SOLVER
>> >> option.- the SOLVER add in must be installed for this to be visible.
>> >>
>> >> --
>> >> Cheers
>> >> Nigel
>> >>
>> >>
>> >>
>> >> "Dmitry" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > Hello
>> >> > I receive:
>> >> > "Microsoft Visual Basic
>> >> > Compile error:
>> >> > Sum or Function not defined" after I have run a macro. I recorded
>> >> > the
>> >> > macro.
>> >> > The macro code is:
>> >> > Sub Macro2()
>> >> > '
>> >> > ' Macro2 Macro
>> >> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
>> >> > '
>> >> > '
>> >> > Range("J16").Select
>> >> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
>> >> > ByChange:="$F$4:$I$12"
>> >> > SolverSolve
>> >> > End Sub
>> >> > All recorded operations work by itself. How to make the macro run
>> >> > the
>> >> > operations?
>>
>>
>
>

Thanks Dana DeLouis for the codes.
Would I have to type in all the selected sheets names in the Demo2 macro?
The selected range includes 40 sheets.
"Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
следующее: news:[email protected]...
> > How to repeat the macro for selected sheets in a Workbook?
>
> Hi. That was for all sheets. Would any ideas here help for selected
> sheets?
>
> Sub Demo()
> Dim sht
> For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
> sht.Activate
> ' Solver here...
> Next
> End Sub
>
> Sub Demo2()
> Dim sht
> For Each sht In Array("Sheet1", "Sheet3", "Sheet5")
> Worksheets(sht).Activate
> ' Solver here...
> Next
> End Sub
>
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Dmitry" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Dana DeLouis.
> > Does your code repeats for all worksheets in a workbook? I wrote "How to
> > repeat the macro for a contiguous range of sheets (not all sheets) in a
> > Workbook?" How to repeat the macro for selected sheets in a Workbook?
> > "Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
> > следующее: news:[email protected]...
> >> Not sure, but see if there are any ideas here that can help.
> >> If not, please post back. :>)
> >>
> >> Sub Demo()
> >> Dim Sht As Worksheet
> >> For Each Sht In ActiveWorkbook.Worksheets
> >> Sht.Activate
> >> SolverReset
> >> SolverOk "J16", 1, , "F4:I12"
> >> SolverSolve True
> >> Next Sht
> >> End Sub
> >>
> >> --
> >> HTH. :>)
> >> Dana DeLouis
> >> Windows XP, Office 2003
> >>
> >>
> >> "Dmitry" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Thanks Nigel!
> >> > How to repeat the macro for a contiguous range of sheets in a
Workbook?
> > I
> >> > call the range contiguous because the range sheets tabs are
contiguous
> > in
> >> > the Workbook.
> >> > How to make the macro close the Solver Results window?
> >> > "Nigel" <[email protected]> сообщил/сообщила в новостях
> >> > следующее:
> >> > news:[email protected]...
> >> >> Solver is not recognised by the standard Excel object model, add a
> >> > reference
> >> >> to SOLVER in your VB Editor.
> >> >>
> >> >> Goto VB Editor (Alt-F11) , select Tools->References then check the
> > SOLVER
> >> >> option.- the SOLVER add in must be installed for this to be visible.
> >> >>
> >> >> --
> >> >> Cheers
> >> >> Nigel
> >> >>
> >> >>
> >> >>
> >> >> "Dmitry" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> > Hello
> >> >> > I receive:
> >> >> > "Microsoft Visual Basic
> >> >> > Compile error:
> >> >> > Sum or Function not defined" after I have run a macro. I recorded
> >> >> > the
> >> >> > macro.
> >> >> > The macro code is:
> >> >> > Sub Macro2()
> >> >> > '
> >> >> > ' Macro2 Macro
> >> >> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> >> >> > '
> >> >> > '
> >> >> > Range("J16").Select
> >> >> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> >> >> > ByChange:="$F$4:$I$12"
> >> >> > SolverSolve
> >> >> > End Sub
> >> >> > All recorded operations work by itself. How to make the macro run
> >> >> > the
> >> >> > operations?
> >>
> >>
> >
> >
>
>

Hi. I'm afraid I don't understand, but I'll try to help. I am assuming you
want to run the Solver code on the same range of cells on 40 different
worksheets. Is that correct?

>> How to repeat the macro for selected sheets in a Workbook?

For this, I was pointing out the "SelectedSheets" property.
One can hold the Control key and select all 40 sheets.
Or, if the sheets are together, one can select the first sheet, hold the
Shift key, and select the last sheet.

> Would I have to type in all the selected sheets names in the Demo2 macro?

Yes. If your sheets were scattered, with no pattern to their names, this
was another method.

> How to repeat the macro for a contiguous range of sheets (not all sheets)

If the sheets are grouped together, this gives us another option.
Suppose the sheets are the first 40 worksheets.

Sub Demo3()
Dim ws As Long
For ws = 1 To 40
Worksheets(ws).Activate
'Solver Code Here...
Next ws
End Sub

If the worksheets that you want to run Solver on begin with a unique name,
here is another option.
Suppose all the sheets that you want to run Solver on begin with "Data",
(ie Data1, Data2, etc...)

Sub Demo4()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "Data*" Then
ws.Activate
'Solver Code Here...
End If
Next ws
End Sub

Hope some of these ideas will work for you problem. :>)
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Dmitry" <[email protected]> wrote in message
news:[email protected]...
> Thanks Dana DeLouis for the codes.
> Would I have to type in all the selected sheets names in the Demo2 macro?
> The selected range includes 40 sheets.
> "Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
> следующее: news:[email protected]...
>> > How to repeat the macro for selected sheets in a Workbook?
>>
>> Hi. That was for all sheets. Would any ideas here help for selected
>> sheets?
>>
>> Sub Demo()
>> Dim sht
>> For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
>> sht.Activate
>> ' Solver here...
>> Next
>> End Sub
>>
>> Sub Demo2()
>> Dim sht
>> For Each sht In Array("Sheet1", "Sheet3", "Sheet5")
>> Worksheets(sht).Activate
>> ' Solver here...
>> Next
>> End Sub
>>
>> --
>> HTH. :>)
>> Dana DeLouis
>> Windows XP, Office 2003
>>
>>
>> "Dmitry" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks Dana DeLouis.
>> > Does your code repeats for all worksheets in a workbook? I wrote "How
>> > to
>> > repeat the macro for a contiguous range of sheets (not all sheets) in a
>> > Workbook?" How to repeat the macro for selected sheets in a Workbook?
>> > "Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
>> > следующее: news:[email protected]...
>> >> Not sure, but see if there are any ideas here that can help.
>> >> If not, please post back. :>)
>> >>
>> >> Sub Demo()
>> >> Dim Sht As Worksheet
>> >> For Each Sht In ActiveWorkbook.Worksheets
>> >> Sht.Activate
>> >> SolverReset
>> >> SolverOk "J16", 1, , "F4:I12"
>> >> SolverSolve True
>> >> Next Sht
>> >> End Sub
>> >>
>> >> --
>> >> HTH. :>)
>> >> Dana DeLouis
>> >> Windows XP, Office 2003
>> >>
>> >>
>> >> "Dmitry" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > Thanks Nigel!
>> >> > How to repeat the macro for a contiguous range of sheets in a
> Workbook?
>> > I
>> >> > call the range contiguous because the range sheets tabs are
> contiguous
>> > in
>> >> > the Workbook.
>> >> > How to make the macro close the Solver Results window?
>> >> > "Nigel" <[email protected]> сообщил/сообщила в новостях
>> >> > следующее:
>> >> > news:[email protected]...
>> >> >> Solver is not recognised by the standard Excel object model, add a
>> >> > reference
>> >> >> to SOLVER in your VB Editor.
>> >> >>
>> >> >> Goto VB Editor (Alt-F11) , select Tools->References then check the
>> > SOLVER
>> >> >> option.- the SOLVER add in must be installed for this to be
>> >> >> visible.
>> >> >>
>> >> >> --
>> >> >> Cheers
>> >> >> Nigel
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Dmitry" <[email protected]> wrote in message
>> >> >> news:%[email protected]...
>> >> >> > Hello
>> >> >> > I receive:
>> >> >> > "Microsoft Visual Basic
>> >> >> > Compile error:
>> >> >> > Sum or Function not defined" after I have run a macro. I recorded
>> >> >> > the
>> >> >> > macro.
>> >> >> > The macro code is:
>> >> >> > Sub Macro2()
>> >> >> > '
>> >> >> > ' Macro2 Macro
>> >> >> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
>> >> >> > '
>> >> >> > '
>> >> >> > Range("J16").Select
>> >> >> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
>> >> >> > ByChange:="$F$4:$I$12"
>> >> >> > SolverSolve
>> >> >> > End Sub
>> >> >> > All recorded operations work by itself. How to make the macro run
>> >> >> > the
>> >> >> > operations?
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Dear Dana DeLouis,
Thanks for your explanations and codes.
Yes, I want to run the Solver code on the same range of cells on 40
different worksheets.
My sheets are with a pattern to their names: for example 95_06 or ДТ_09, two
digits or letters, the underline and two digits.
"Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
следующее: news:[email protected]...
> Hi. I'm afraid I don't understand, but I'll try to help. I am assuming
you
> want to run the Solver code on the same range of cells on 40 different
> worksheets. Is that correct?
>
> >> How to repeat the macro for selected sheets in a Workbook?
>
> For this, I was pointing out the "SelectedSheets" property.
> One can hold the Control key and select all 40 sheets.
> Or, if the sheets are together, one can select the first sheet, hold the
> Shift key, and select the last sheet.
>
> > Would I have to type in all the selected sheets names in the Demo2
macro?
>
> Yes. If your sheets were scattered, with no pattern to their names, this
> was another method.
>
> > How to repeat the macro for a contiguous range of sheets (not all
sheets)
>
> If the sheets are grouped together, this gives us another option.
> Suppose the sheets are the first 40 worksheets.
>
> Sub Demo3()
> Dim ws As Long
> For ws = 1 To 40
> Worksheets(ws).Activate
> 'Solver Code Here...
> Next ws
> End Sub
>
> If the worksheets that you want to run Solver on begin with a unique name,
> here is another option.
> Suppose all the sheets that you want to run Solver on begin with "Data",
> (ie Data1, Data2, etc...)
>
> Sub Demo4()
> Dim ws As Worksheet
> For Each ws In ActiveWorkbook.Worksheets
> If ws.Name Like "Data*" Then
> ws.Activate
> 'Solver Code Here...
> End If
> Next ws
> End Sub
>
> Hope some of these ideas will work for you problem. :>)
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Dmitry" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Dana DeLouis for the codes.
> > Would I have to type in all the selected sheets names in the Demo2
macro?
> > The selected range includes 40 sheets.
> > "Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
> > следующее: news:[email protected]...
> >> > How to repeat the macro for selected sheets in a Workbook?
> >>
> >> Hi. That was for all sheets. Would any ideas here help for selected
> >> sheets?
> >>
> >> Sub Demo()
> >> Dim sht
> >> For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
> >> sht.Activate
> >> ' Solver here...
> >> Next
> >> End Sub
> >>
> >> Sub Demo2()
> >> Dim sht
> >> For Each sht In Array("Sheet1", "Sheet3", "Sheet5")
> >> Worksheets(sht).Activate
> >> ' Solver here...
> >> Next
> >> End Sub
> >>
> >> --
> >> HTH. :>)
> >> Dana DeLouis
> >> Windows XP, Office 2003
> >>
> >>
> >> "Dmitry" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Thanks Dana DeLouis.
> >> > Does your code repeats for all worksheets in a workbook? I wrote "How
> >> > to
> >> > repeat the macro for a contiguous range of sheets (not all sheets) in
a
> >> > Workbook?" How to repeat the macro for selected sheets in a Workbook?
> >> > "Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
> >> > следующее: news:[email protected]...
> >> >> Not sure, but see if there are any ideas here that can help.
> >> >> If not, please post back. :>)
> >> >>
> >> >> Sub Demo()
> >> >> Dim Sht As Worksheet
> >> >> For Each Sht In ActiveWorkbook.Worksheets
> >> >> Sht.Activate
> >> >> SolverReset
> >> >> SolverOk "J16", 1, , "F4:I12"
> >> >> SolverSolve True
> >> >> Next Sht
> >> >> End Sub
> >> >>
> >> >> --
> >> >> HTH. :>)
> >> >> Dana DeLouis
> >> >> Windows XP, Office 2003
> >> >>
> >> >>
> >> >> "Dmitry" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> > Thanks Nigel!
> >> >> > How to repeat the macro for a contiguous range of sheets in a
> > Workbook?
> >> > I
> >> >> > call the range contiguous because the range sheets tabs are
> > contiguous
> >> > in
> >> >> > the Workbook.
> >> >> > How to make the macro close the Solver Results window?
> >> >> > "Nigel" <[email protected]> сообщил/сообщила в новостях
> >> >> > следующее:
> >> >> > news:[email protected]...
> >> >> >> Solver is not recognised by the standard Excel object model, add
a
> >> >> > reference
> >> >> >> to SOLVER in your VB Editor.
> >> >> >>
> >> >> >> Goto VB Editor (Alt-F11) , select Tools->References then check
the
> >> > SOLVER
> >> >> >> option.- the SOLVER add in must be installed for this to be
> >> >> >> visible.
> >> >> >>
> >> >> >> --
> >> >> >> Cheers
> >> >> >> Nigel
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> "Dmitry" <[email protected]> wrote in message
> >> >> >> news:%[email protected]...
> >> >> >> > Hello
> >> >> >> > I receive:
> >> >> >> > "Microsoft Visual Basic
> >> >> >> > Compile error:
> >> >> >> > Sum or Function not defined" after I have run a macro. I
recorded
> >> >> >> > the
> >> >> >> > macro.
> >> >> >> > The macro code is:
> >> >> >> > Sub Macro2()
> >> >> >> > '
> >> >> >> > ' Macro2 Macro
> >> >> >> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> >> >> >> > '
> >> >> >> > '
> >> >> >> > Range("J16").Select
> >> >> >> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> >> >> >> > ByChange:="$F$4:$I$12"
> >> >> >> > SolverSolve
> >> >> >> > End Sub
> >> >> >> > All recorded operations work by itself. How to make the macro
run
> >> >> >> > the
> >> >> >> > operations?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Dear Dana DeLouis,
What does (1) mean in Sub Demo()
> Dim sht
> For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
> sht.Activate
> ' Solver here...
> Next
> End Sub?
"Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
следующее: news:[email protected]...
> > How to repeat the macro for selected sheets in a Workbook?
>
> Hi. That was for all sheets. Would any ideas here help for selected
> sheets?
>
> Sub Demo()
> Dim sht
> For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
> sht.Activate
> ' Solver here...
> Next
> End Sub
>
> Sub Demo2()
> Dim sht
> For Each sht In Array("Sheet1", "Sheet3", "Sheet5")
> Worksheets(sht).Activate
> ' Solver here...
> Next
> End Sub
>
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Dmitry" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Dana DeLouis.
> > Does your code repeats for all worksheets in a workbook? I wrote "How to
> > repeat the macro for a contiguous range of sheets (not all sheets) in a
> > Workbook?" How to repeat the macro for selected sheets in a Workbook?
> > "Dana DeLouis" <[email protected]> сообщил/сообщила в новостях
> > следующее: news:[email protected]...
> >> Not sure, but see if there are any ideas here that can help.
> >> If not, please post back. :>)
> >>
> >> Sub Demo()
> >> Dim Sht As Worksheet
> >> For Each Sht In ActiveWorkbook.Worksheets
> >> Sht.Activate
> >> SolverReset
> >> SolverOk "J16", 1, , "F4:I12"
> >> SolverSolve True
> >> Next Sht
> >> End Sub
> >>
> >> --
> >> HTH. :>)
> >> Dana DeLouis
> >> Windows XP, Office 2003
> >>
> >>
> >> "Dmitry" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Thanks Nigel!
> >> > How to repeat the macro for a contiguous range of sheets in a
Workbook?
> > I
> >> > call the range contiguous because the range sheets tabs are
contiguous
> > in
> >> > the Workbook.
> >> > How to make the macro close the Solver Results window?
> >> > "Nigel" <[email protected]> сообщил/сообщила в новостях
> >> > следующее:
> >> > news:[email protected]...
> >> >> Solver is not recognised by the standard Excel object model, add a
> >> > reference
> >> >> to SOLVER in your VB Editor.
> >> >>
> >> >> Goto VB Editor (Alt-F11) , select Tools->References then check the
> > SOLVER
> >> >> option.- the SOLVER add in must be installed for this to be visible.
> >> >>
> >> >> --
> >> >> Cheers
> >> >> Nigel
> >> >>
> >> >>
> >> >>
> >> >> "Dmitry" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> > Hello
> >> >> > I receive:
> >> >> > "Microsoft Visual Basic
> >> >> > Compile error:
> >> >> > Sum or Function not defined" after I have run a macro. I recorded
> >> >> > the
> >> >> > macro.
> >> >> > The macro code is:
> >> >> > Sub Macro2()
> >> >> > '
> >> >> > ' Macro2 Macro
> >> >> > ' Macro recorded 24.03.2006 by Dmitry Kopnichev
> >> >> > '
> >> >> > '
> >> >> > Range("J16").Select
> >> >> > SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
> >> >> > ByChange:="$F$4:$I$12"
> >> >> > SolverSolve
> >> >> > End Sub
> >> >> > All recorded operations work by itself. How to make the macro run
> >> >> > the
> >> >> > operations?
> >>
> >>
> >
> >
>
>