code question

i have a routine i wrote to add a range to an exiting formula. it has 24
formulas on 12 sheets hat need to be updated. the linked cells are an
external workbook with a sheet called branch.

the routine takes about a minute and a half to execute, but it will only
execute when a new person is added, couple times a year.

it is about 50 lines including the dims. is it appropriate to post it so i
can ask someone if there is a more efficient way?

thanks

--


Gary

Go ahead and post it. I'm sure someone will be able to take it on.

Gary Keramidas wrote:
> i have a routine i wrote to add a range to an exiting formula. it has 24
> formulas on 12 sheets hat need to be updated. the linked cells are an
> external workbook with a sheet called branch.
>
> the routine takes about a minute and a half to execute, but it will only
> execute when a new person is added, couple times a year.
>
> it is about 50 lines including the dims. is it appropriate to post it so i
> can ask someone if there is a more efficient way?
>
> thanks
>

ok, here it is. i used an attachment to help with line breaks

--


Gary


"Gareth" <msng@garhooREMOVEME.com> wrote in message
news:O95zlGBjFHA.1480@TK2MSFTNGP10.phx.gbl...
> Go ahead and post it. I'm sure someone will be able to take it on.
>
> Gary Keramidas wrote:
>> i have a routine i wrote to add a range to an exiting formula. it has 24
>> formulas on 12 sheets hat need to be updated. the linked cells are an
>> external workbook with a sheet called branch.
>>
>> the routine takes about a minute and a half to execute, but it will only
>> execute when a new person is added, couple times a year.
>>
>> it is about 50 lines including the dims. is it appropriate to post it so
>> i
>> can ask someone if there is a more efficient way?
>>
>> thanks
>>

Yes, OK to post the code.

Tim


"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:eZaXLIAjFHA.3064@TK2MSFTNGP15.phx.gbl...
>i have a routine i wrote to add a range to an exiting formula. it has
>24 formulas on 12 sheets hat need to be updated. the linked cells are
>an external workbook with a sheet called branch.
>
> the routine takes about a minute and a half to execute, but it will
> only execute when a new person is added, couple times a year.
>
> it is about 50 lines including the dims. is it appropriate to post
> it so i can ask someone if there is a more efficient way?
>
> thanks
>
> --
>
>
> Gary
>
>
>

Don't post attachments: no-one will read them. Paste the code into the
message body - work on line breaks as required.

Tim

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:%23A7fPKBjFHA.1416@TK2MSFTNGP09.phx.gbl...
> ok, here it is. i used an attachment to help with line breaks
>
> --
>
>
> Gary
>
>
> "Gareth" <msng@garhooREMOVEME.com> wrote in message
> news:O95zlGBjFHA.1480@TK2MSFTNGP10.phx.gbl...
>> Go ahead and post it. I'm sure someone will be able to take it on.
>>
>> Gary Keramidas wrote:
>>> i have a routine i wrote to add a range to an exiting formula. it
>>> has 24
>>> formulas on 12 sheets hat need to be updated. the linked cells are
>>> an
>>> external workbook with a sheet called branch.
>>>
>>> the routine takes about a minute and a half to execute, but it
>>> will only
>>> execute when a new person is added, couple times a year.
>>>
>>> it is about 50 lines including the dims. is it appropriate to post
>>> it so i
>>> can ask someone if there is a more efficient way?
>>>
>>> thanks
>>>
>
>
>

ok

Option Explicit
Dim uName As String
Dim rNum As Integer
Dim mName As Integer
Dim cnt As Integer
Dim Counter As Integer

Sub Add_User()

Application.ScreenUpdating = False

Counter = 1
cnt = 1
mName = 4
uName = "New Name Here" ' enter new person's name (workbook name)
rNum = 39 + mName ' starting row for data in new sheet (43)


Do
For Counter = 1 To 12 Step 1
Sheets(mName).Select
With Sheets(mName)
ActiveSheet.Unprotect
.Range("C5").Formula = .Range("c5").Formula & "+[" & uName &
".xls]Branch!$C$" & rNum & ""
.Range("C6").Formula = .Range("c6").Formula & "+[" & uName &
".xls]Branch!$F$" & rNum & ""

.Range("D5").Formula = .Range("D5").Formula & "+[" & uName &
".xls]Branch!$D$" & rNum & ""
.Range("D6").Formula = .Range("D6").Formula & "+[" & uName &
".xls]Branch!$G$" & rNum & ""

.Range("E5").Formula = .Range("E5").Formula & "+[" & uName &
".xls]Branch!$H$" & rNum & ""
.Range("E6").Formula = .Range("E6").Formula & "+[" & uName &
".xls]Branch!$I$" & rNum & ""

rNum = rNum + 14
.Range("C7").Formula = .Range("c7").Formula & "+[" & uName &
".xls]Branch!$C$" & rNum & ""
.Range("C8").Formula = .Range("c8").Formula & "+[" & uName &
".xls]Branch!$F$" & rNum & ""

.Range("D7").Formula = .Range("D7").Formula & "+[" & uName &
".xls]Branch!$D$" & rNum & ""
.Range("D8").Formula = .Range("D8").Formula & "+[" & uName &
".xls]Branch!$G$" & rNum & ""

.Range("E7").Formula = .Range("E7").Formula & "+[" & uName &
".xls]Branch!$H$" & rNum & ""
.Range("E8").Formula = .Range("E8").Formula & "+[" & uName &
".xls]Branch!$I$" & rNum & ""

rNum = rNum + 14
.Range("C9").Formula = .Range("c9").Formula & "+[" & uName &
".xls]Branch!$C$" & rNum & ""
.Range("C10").Formula = .Range("c10").Formula & "+[" & uName &
".xls]Branch!$F$" & rNum & ""

.Range("D9").Formula = .Range("D9").Formula & "+[" & uName &
".xls]Branch!$D$" & rNum & ""
.Range("D10").Formula = .Range("D10").Formula & "+[" & uName &
".xls]Branch!$G$" & rNum & ""

.Range("E9").Formula = .Range("E9").Formula & "+[" & uName &
".xls]Branch!$H$" & rNum & ""
.Range("E10").Formula = .Range("E10").Formula & "+[" & uName &
".xls]Branch!$I$" & rNum & ""


rNum = rNum + 14
.Range("C11").Formula = .Range("c11").Formula & "+[" & uName &
".xls]Branch!$C$" & rNum & ""
.Range("C12").Formula = .Range("c12").Formula & "+[" & uName &
".xls]Branch!$F$" & rNum & ""

.Range("D11").Formula = .Range("D11").Formula & "+[" & uName &
".xls]Branch!$D$" & rNum & ""
.Range("D12").Formula = .Range("D12").Formula & "+[" & uName &
".xls]Branch!$G$" & rNum & ""

.Range("E11").Formula = .Range("E11").Formula & "+[" & uName &
".xls]Branch!$H$" & rNum & ""
.Range("E12").Formula = .Range("E12").Formula & "+[" & uName &
".xls]Branch!$I$" & rNum & ""


End With
mName = mName + 1
rNum = 39 + mName
ActiveSheet.Protect
Next Counter
Exit Do
Loop
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

--


Gary


"Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
news:OT3VbVBjFHA.2444@tk2msftngp13.phx.gbl...
> Don't post attachments: no-one will read them. Paste the code into the
> message body - work on line breaks as required.
>
> Tim
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:%23A7fPKBjFHA.1416@TK2MSFTNGP09.phx.gbl...
>> ok, here it is. i used an attachment to help with line breaks
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Gareth" <msng@garhooREMOVEME.com> wrote in message
>> news:O95zlGBjFHA.1480@TK2MSFTNGP10.phx.gbl...
>>> Go ahead and post it. I'm sure someone will be able to take it on.
>>>
>>> Gary Keramidas wrote:
>>>> i have a routine i wrote to add a range to an exiting formula. it has
>>>> 24
>>>> formulas on 12 sheets hat need to be updated. the linked cells are an
>>>> external workbook with a sheet called branch.
>>>>
>>>> the routine takes about a minute and a half to execute, but it will
>>>> only
>>>> execute when a new person is added, couple times a year.
>>>>
>>>> it is about 50 lines including the dims. is it appropriate to post it
>>>> so i
>>>> can ask someone if there is a more efficient way?
>>>>
>>>> thanks
>>>>
>>
>>
>>
>
>

Hi Gary,

Independently of your code, I would suggest that you expand the ScreenUpdate
wrapper to include calculation and (while you are at it) also include
pagebreak displays. somehing like:

Dim CalcMode As Long
Dim PgBreakMode As Boolean

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
PgBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With


' Your code



With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveSheet.DisplayPageBreaks = PgBreakMode

End Sub

---
Regards,
Norman

hi norman:
i added your code. it ran ok, still took a minute and a half. each formula
has 12 links to other files, so they are quite long. they're about 738
characters long to begin with.

thanks for the info and code
-
-


Gary


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:e2$fehBjFHA.3912@TK2MSFTNGP10.phx.gbl...
> Hi Gary,
>
> Independently of your code, I would suggest that you expand the
> ScreenUpdate wrapper to include calculation and (while you are at it) also
> include pagebreak displays. somehing like:
>
> Dim CalcMode As Long
> Dim PgBreakMode As Boolean
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> With ActiveSheet
> PgBreakMode = .DisplayPageBreaks
> .DisplayPageBreaks = False
> End With
>
>
> ' Your code
>
>
>
> With Application
> .Calculation = CalcMode
> .ScreenUpdating = True
> End With
>
> ActiveSheet.DisplayPageBreaks = PgBreakMode
>
> End Sub
>
> ---
> Regards,
> Norman
>
>

BTW, it took about 1 sec on my machine, what do you have?

--
HTH

Bob Phillips

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:Oa9YJACjFHA.1232@TK2MSFTNGP15.phx.gbl...
> hi norman:
> i added your code. it ran ok, still took a minute and a half. each formula
> has 12 links to other files, so they are quite long. they're about 738
> characters long to begin with.
>
> thanks for the info and code
> -
> -
>
>
> Gary
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:e2$fehBjFHA.3912@TK2MSFTNGP10.phx.gbl...
> > Hi Gary,
> >
> > Independently of your code, I would suggest that you expand the
> > ScreenUpdate wrapper to include calculation and (while you are at it)
also
> > include pagebreak displays. somehing like:
> >
> > Dim CalcMode As Long
> > Dim PgBreakMode As Boolean
> >
> > With Application
> > CalcMode = .Calculation
> > .Calculation = xlCalculationManual
> > .ScreenUpdating = False
> > End With
> >
> > With ActiveSheet
> > PgBreakMode = .DisplayPageBreaks
> > .DisplayPageBreaks = False
> > End With
> >
> >
> > ' Your code
> >
> >
> >
> > With Application
> > .Calculation = CalcMode
> > .ScreenUpdating = True
> > End With
> >
> > ActiveSheet.DisplayPageBreaks = PgBreakMode
> >
> > End Sub
> >
> > ---
> > Regards,
> > Norman
> >
> >
>
>

Looked at it some more and the loop is also unnecessary. Just use an Excel
formula that sums

Sub Add_User()

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
PgBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With

cnt = 1
mName = 4
uName = "Bob" ' enter new person's name (workbook name)
Rnum = 39 + mName ' starting row for data in new sheet (43)

With Sheets(mName)
.Unprotect
.Range("C5").Formula = .Range("C5").Formula & _
"+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11
& ")"
.Range("C6").Formula = .Range("C6").Formula & _
"+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11
& ")"

.Range("D5").Formula = .Range("D5").Formula & _
"+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11
& ")"
.Range("D6").Formula = .Range("D6").Formula & _
"+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11
& ")"

.Range("E5").Formula = .Range("E5").Formula & _
"+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11
& ")"
.Range("E6").Formula = .Range("E6").Formula & _
"+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11
& ")"

Rnum = Rnum + 14
.Range("C7").Formula = .Range("c7").Formula & _
"+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11
& ")"
.Range("C8").Formula = .Range("c8").Formula & _
"+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11
& ")"

.Range("D7").Formula = .Range("D7").Formula & _
"+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11
& ")"
.Range("D8").Formula = .Range("D8").Formula & _
"+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11
& ")"

.Range("E7").Formula = .Range("E7").Formula & _
"+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11
& ")"
.Range("E8").Formula = .Range("E8").Formula & _
"+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11
& ")"

Rnum = Rnum + 14
.Range("C9").Formula = .Range("c9").Formula & _
"+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11
& ")"
.Range("C10").Formula = .Range("c10").Formula & _
"+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11
& ")"

.Range("D9").Formula = .Range("D9").Formula & _
"+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11
& ")"
.Range("D10").Formula = .Range("D10").Formula & _
"+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11
& ")"

.Range("E9").Formula = .Range("E9").Formula & _
"+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum + 11
& ")"
.Range("E10").Formula = .Range("E10").Formula & _
"+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11
& ")"

Rnum = Rnum + 14
.Range("C11").Formula = .Range("C11").Formula & _
"+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum + 11
& ")"
.Range("C12").Formula = .Range("C12").Formula & _
"+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum + 11
& ")"

.Range("D11").Formula = .Range("D11").Formula & _
"+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum + 11
& ")"
.Range("D12").Formula = .Range("D12").Formula & _
"+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum + 11
& ")"

.Range("E11").Formula = .Range("E11").Formula & _
"+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$H$" & Rnum + 11
& ")"
.Range("E12").Formula = .Range("E12").Formula & _
"+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum + 11
& ")"

mName = mName + 1
Rnum = 39 + mName
.Protect
End With

With Application
CalcMode = .Calculation
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

MsgBox "Done"

End Sub


--
HTH

Bob Phillips

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:urpY3ZBjFHA.3784@tk2msftngp13.phx.gbl...
> ok
>
> Option Explicit
> Dim uName As String
> Dim rNum As Integer
> Dim mName As Integer
> Dim cnt As Integer
> Dim Counter As Integer
>
> Sub Add_User()
>
> Application.ScreenUpdating = False
>
> Counter = 1
> cnt = 1
> mName = 4
> uName = "New Name Here" ' enter new person's name (workbook name)
> rNum = 39 + mName ' starting row for data in new sheet (43)
>
>
> Do
> For Counter = 1 To 12 Step 1
> Sheets(mName).Select
> With Sheets(mName)
> ActiveSheet.Unprotect
> .Range("C5").Formula = .Range("c5").Formula & "+[" & uName &
> ".xls]Branch!$C$" & rNum & ""
> .Range("C6").Formula = .Range("c6").Formula & "+[" & uName &
> ".xls]Branch!$F$" & rNum & ""
>
> .Range("D5").Formula = .Range("D5").Formula & "+[" & uName &
> ".xls]Branch!$D$" & rNum & ""
> .Range("D6").Formula = .Range("D6").Formula & "+[" & uName &
> ".xls]Branch!$G$" & rNum & ""
>
> .Range("E5").Formula = .Range("E5").Formula & "+[" & uName &
> ".xls]Branch!$H$" & rNum & ""
> .Range("E6").Formula = .Range("E6").Formula & "+[" & uName &
> ".xls]Branch!$I$" & rNum & ""
>
> rNum = rNum + 14
> .Range("C7").Formula = .Range("c7").Formula & "+[" & uName &
> ".xls]Branch!$C$" & rNum & ""
> .Range("C8").Formula = .Range("c8").Formula & "+[" & uName &
> ".xls]Branch!$F$" & rNum & ""
>
> .Range("D7").Formula = .Range("D7").Formula & "+[" & uName &
> ".xls]Branch!$D$" & rNum & ""
> .Range("D8").Formula = .Range("D8").Formula & "+[" & uName &
> ".xls]Branch!$G$" & rNum & ""
>
> .Range("E7").Formula = .Range("E7").Formula & "+[" & uName &
> ".xls]Branch!$H$" & rNum & ""
> .Range("E8").Formula = .Range("E8").Formula & "+[" & uName &
> ".xls]Branch!$I$" & rNum & ""
>
> rNum = rNum + 14
> .Range("C9").Formula = .Range("c9").Formula & "+[" & uName &
> ".xls]Branch!$C$" & rNum & ""
> .Range("C10").Formula = .Range("c10").Formula & "+[" & uName &
> ".xls]Branch!$F$" & rNum & ""
>
> .Range("D9").Formula = .Range("D9").Formula & "+[" & uName &
> ".xls]Branch!$D$" & rNum & ""
> .Range("D10").Formula = .Range("D10").Formula & "+[" & uName &
> ".xls]Branch!$G$" & rNum & ""
>
> .Range("E9").Formula = .Range("E9").Formula & "+[" & uName &
> ".xls]Branch!$H$" & rNum & ""
> .Range("E10").Formula = .Range("E10").Formula & "+[" & uName &
> ".xls]Branch!$I$" & rNum & ""
>
>
> rNum = rNum + 14
> .Range("C11").Formula = .Range("c11").Formula & "+[" & uName &
> ".xls]Branch!$C$" & rNum & ""
> .Range("C12").Formula = .Range("c12").Formula & "+[" & uName &
> ".xls]Branch!$F$" & rNum & ""
>
> .Range("D11").Formula = .Range("D11").Formula & "+[" & uName &
> ".xls]Branch!$D$" & rNum & ""
> .Range("D12").Formula = .Range("D12").Formula & "+[" & uName &
> ".xls]Branch!$G$" & rNum & ""
>
> .Range("E11").Formula = .Range("E11").Formula & "+[" & uName &
> ".xls]Branch!$H$" & rNum & ""
> .Range("E12").Formula = .Range("E12").Formula & "+[" & uName &
> ".xls]Branch!$I$" & rNum & ""
>
>
> End With
> mName = mName + 1
> rNum = 39 + mName
> ActiveSheet.Protect
> Next Counter
> Exit Do
> Loop
> Application.ScreenUpdating = True
> MsgBox "Done"
> End Sub
>
> --
>
>
> Gary
>
>
> "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
> news:OT3VbVBjFHA.2444@tk2msftngp13.phx.gbl...
> > Don't post attachments: no-one will read them. Paste the code into the
> > message body - work on line breaks as required.
> >
> > Tim
> >
> > "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> > news:%23A7fPKBjFHA.1416@TK2MSFTNGP09.phx.gbl...
> >> ok, here it is. i used an attachment to help with line breaks
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >> "Gareth" <msng@garhooREMOVEME.com> wrote in message
> >> news:O95zlGBjFHA.1480@TK2MSFTNGP10.phx.gbl...
> >>> Go ahead and post it. I'm sure someone will be able to take it on.
> >>>
> >>> Gary Keramidas wrote:
> >>>> i have a routine i wrote to add a range to an exiting formula. it has
> >>>> 24
> >>>> formulas on 12 sheets hat need to be updated. the linked cells are an
> >>>> external workbook with a sheet called branch.
> >>>>
> >>>> the routine takes about a minute and a half to execute, but it will
> >>>> only
> >>>> execute when a new person is added, couple times a year.
> >>>>
> >>>> it is about 50 lines including the dims. is it appropriate to post it
> >>>> so i
> >>>> can ask someone if there is a more efficient way?
> >>>>
> >>>> thanks
> >>>>
> >>
> >>
> >>
> >
> >
>
>

Seems odd it should take so long, so we are dealing with small improvements
here, but here are a couple more.

The Do loop seems totally superfluous, so remove the lines

Do
Exit Do
Loop

As you only deal with one sheet, no need to select it each loop, and the
With reference can be outside the Loop

With Sheets(mName)
For Counter = 1 To 12 Step 1
.Unprotect

'main code

mName = mName + 1
rNum = 39 + mName
ActiveSheet.Protect
Next Counter

End With

--
HTH

Bob Phillips

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:urpY3ZBjFHA.3784@tk2msftngp13.phx.gbl...
> ok
>
> Option Explicit
> Dim uName As String
> Dim rNum As Integer
> Dim mName As Integer
> Dim cnt As Integer
> Dim Counter As Integer
>
> Sub Add_User()
>
> Application.ScreenUpdating = False
>
> Counter = 1
> cnt = 1
> mName = 4
> uName = "New Name Here" ' enter new person's name (workbook name)
> rNum = 39 + mName ' starting row for data in new sheet (43)
>
>
> Do
> For Counter = 1 To 12 Step 1
> Sheets(mName).Select
> With Sheets(mName)
> ActiveSheet.Unprotect
> .Range("C5").Formula = .Range("c5").Formula & "+[" & uName &
> ".xls]Branch!$C$" & rNum & ""
> .Range("C6").Formula = .Range("c6").Formula & "+[" & uName &
> ".xls]Branch!$F$" & rNum & ""
>
> .Range("D5").Formula = .Range("D5").Formula & "+[" & uName &
> ".xls]Branch!$D$" & rNum & ""
> .Range("D6").Formula = .Range("D6").Formula & "+[" & uName &
> ".xls]Branch!$G$" & rNum & ""
>
> .Range("E5").Formula = .Range("E5").Formula & "+[" & uName &
> ".xls]Branch!$H$" & rNum & ""
> .Range("E6").Formula = .Range("E6").Formula & "+[" & uName &
> ".xls]Branch!$I$" & rNum & ""
>
> rNum = rNum + 14
> .Range("C7").Formula = .Range("c7").Formula & "+[" & uName &
> ".xls]Branch!$C$" & rNum & ""
> .Range("C8").Formula = .Range("c8").Formula & "+[" & uName &
> ".xls]Branch!$F$" & rNum & ""
>
> .Range("D7").Formula = .Range("D7").Formula & "+[" & uName &
> ".xls]Branch!$D$" & rNum & ""
> .Range("D8").Formula = .Range("D8").Formula & "+[" & uName &
> ".xls]Branch!$G$" & rNum & ""
>
> .Range("E7").Formula = .Range("E7").Formula & "+[" & uName &
> ".xls]Branch!$H$" & rNum & ""
> .Range("E8").Formula = .Range("E8").Formula & "+[" & uName &
> ".xls]Branch!$I$" & rNum & ""
>
> rNum = rNum + 14
> .Range("C9").Formula = .Range("c9").Formula & "+[" & uName &
> ".xls]Branch!$C$" & rNum & ""
> .Range("C10").Formula = .Range("c10").Formula & "+[" & uName &
> ".xls]Branch!$F$" & rNum & ""
>
> .Range("D9").Formula = .Range("D9").Formula & "+[" & uName &
> ".xls]Branch!$D$" & rNum & ""
> .Range("D10").Formula = .Range("D10").Formula & "+[" & uName &
> ".xls]Branch!$G$" & rNum & ""
>
> .Range("E9").Formula = .Range("E9").Formula & "+[" & uName &
> ".xls]Branch!$H$" & rNum & ""
> .Range("E10").Formula = .Range("E10").Formula & "+[" & uName &
> ".xls]Branch!$I$" & rNum & ""
>
>
> rNum = rNum + 14
> .Range("C11").Formula = .Range("c11").Formula & "+[" & uName &
> ".xls]Branch!$C$" & rNum & ""
> .Range("C12").Formula = .Range("c12").Formula & "+[" & uName &
> ".xls]Branch!$F$" & rNum & ""
>
> .Range("D11").Formula = .Range("D11").Formula & "+[" & uName &
> ".xls]Branch!$D$" & rNum & ""
> .Range("D12").Formula = .Range("D12").Formula & "+[" & uName &
> ".xls]Branch!$G$" & rNum & ""
>
> .Range("E11").Formula = .Range("E11").Formula & "+[" & uName &
> ".xls]Branch!$H$" & rNum & ""
> .Range("E12").Formula = .Range("E12").Formula & "+[" & uName &
> ".xls]Branch!$I$" & rNum & ""
>
>
> End With
> mName = mName + 1
> rNum = 39 + mName
> ActiveSheet.Protect
> Next Counter
> Exit Do
> Loop
> Application.ScreenUpdating = True
> MsgBox "Done"
> End Sub
>
> --
>
>
> Gary
>
>
> "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
> news:OT3VbVBjFHA.2444@tk2msftngp13.phx.gbl...
> > Don't post attachments: no-one will read them. Paste the code into the
> > message body - work on line breaks as required.
> >
> > Tim
> >
> > "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> > news:%23A7fPKBjFHA.1416@TK2MSFTNGP09.phx.gbl...
> >> ok, here it is. i used an attachment to help with line breaks
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >> "Gareth" <msng@garhooREMOVEME.com> wrote in message
> >> news:O95zlGBjFHA.1480@TK2MSFTNGP10.phx.gbl...
> >>> Go ahead and post it. I'm sure someone will be able to take it on.
> >>>
> >>> Gary Keramidas wrote:
> >>>> i have a routine i wrote to add a range to an exiting formula. it has
> >>>> 24
> >>>> formulas on 12 sheets hat need to be updated. the linked cells are an
> >>>> external workbook with a sheet called branch.
> >>>>
> >>>> the routine takes about a minute and a half to execute, but it will
> >>>> only
> >>>> execute when a new person is added, couple times a year.
> >>>>
> >>>> it is about 50 lines including the dims. is it appropriate to post it
> >>>> so i
> >>>> can ask someone if there is a more efficient way?
> >>>>
> >>>> thanks
> >>>>
> >>
> >>
> >>
> >
> >
>
>

it takes about a second here with blank worksheets. i have an athlon64 3200
with a gb of ram.

--


Gary


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:uZBQwpDjFHA.1480@TK2MSFTNGP10.phx.gbl...
> BTW, it took about 1 sec on my machine, what do you have?
>
> --
> HTH
>
> Bob Phillips
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:Oa9YJACjFHA.1232@TK2MSFTNGP15.phx.gbl...
>> hi norman:
>> i added your code. it ran ok, still took a minute and a half. each
>> formula
>> has 12 links to other files, so they are quite long. they're about 738
>> characters long to begin with.
>>
>> thanks for the info and code
>> -
>> -
>>
>>
>> Gary
>>
>>
>> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
>> news:e2$fehBjFHA.3912@TK2MSFTNGP10.phx.gbl...
>> > Hi Gary,
>> >
>> > Independently of your code, I would suggest that you expand the
>> > ScreenUpdate wrapper to include calculation and (while you are at it)
> also
>> > include pagebreak displays. somehing like:
>> >
>> > Dim CalcMode As Long
>> > Dim PgBreakMode As Boolean
>> >
>> > With Application
>> > CalcMode = .Calculation
>> > .Calculation = xlCalculationManual
>> > .ScreenUpdating = False
>> > End With
>> >
>> > With ActiveSheet
>> > PgBreakMode = .DisplayPageBreaks
>> > .DisplayPageBreaks = False
>> > End With
>> >
>> >
>> > ' Your code
>> >
>> >
>> >
>> > With Application
>> > .Calculation = CalcMode
>> > .ScreenUpdating = True
>> > End With
>> >
>> > ActiveSheet.DisplayPageBreaks = PgBreakMode
>> >
>> > End Sub
>> >
>> > ---
>> > Regards,
>> > Norman
>> >
>> >
>>
>>
>
>

bob:

this appears to work faster, it just adds a range to the existing formula
and all i need is one cell. i'll change the your code back so it just a
single cell reference and let you know how it goes.

[Manager.xls]Branch'!$D$43:$D$54
instead of
[Manager.xls]Branch'!$D$43
thanks for looking at this for me.

--


Gary


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:%23k5aMzDjFHA.3912@TK2MSFTNGP10.phx.gbl...
> Looked at it some more and the loop is also unnecessary. Just use an Excel
> formula that sums
>
> Sub Add_User()
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> With ActiveSheet
> PgBreakMode = .DisplayPageBreaks
> .DisplayPageBreaks = False
> End With
>
> cnt = 1
> mName = 4
> uName = "Bob" ' enter new person's name (workbook name)
> Rnum = 39 + mName ' starting row for data in new sheet (43)
>
> With Sheets(mName)
> .Unprotect
> .Range("C5").Formula = .Range("C5").Formula & _
> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
> 11
> & ")"
> .Range("C6").Formula = .Range("C6").Formula & _
> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
> 11
> & ")"
>
> .Range("D5").Formula = .Range("D5").Formula & _
> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
> 11
> & ")"
> .Range("D6").Formula = .Range("D6").Formula & _
> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
> 11
> & ")"
>
> .Range("E5").Formula = .Range("E5").Formula & _
> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
> 11
> & ")"
> .Range("E6").Formula = .Range("E6").Formula & _
> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
> 11
> & ")"
>
> Rnum = Rnum + 14
> .Range("C7").Formula = .Range("c7").Formula & _
> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
> 11
> & ")"
> .Range("C8").Formula = .Range("c8").Formula & _
> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
> 11
> & ")"
>
> .Range("D7").Formula = .Range("D7").Formula & _
> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
> 11
> & ")"
> .Range("D8").Formula = .Range("D8").Formula & _
> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
> 11
> & ")"
>
> .Range("E7").Formula = .Range("E7").Formula & _
> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
> 11
> & ")"
> .Range("E8").Formula = .Range("E8").Formula & _
> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
> 11
> & ")"
>
> Rnum = Rnum + 14
> .Range("C9").Formula = .Range("c9").Formula & _
> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
> 11
> & ")"
> .Range("C10").Formula = .Range("c10").Formula & _
> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
> 11
> & ")"
>
> .Range("D9").Formula = .Range("D9").Formula & _
> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
> 11
> & ")"
> .Range("D10").Formula = .Range("D10").Formula & _
> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
> 11
> & ")"
>
> .Range("E9").Formula = .Range("E9").Formula & _
> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
> 11
> & ")"
> .Range("E10").Formula = .Range("E10").Formula & _
> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
> 11
> & ")"
>
> Rnum = Rnum + 14
> .Range("C11").Formula = .Range("C11").Formula & _
> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
> 11
> & ")"
> .Range("C12").Formula = .Range("C12").Formula & _
> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
> 11
> & ")"
>
> .Range("D11").Formula = .Range("D11").Formula & _
> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
> 11
> & ")"
> .Range("D12").Formula = .Range("D12").Formula & _
> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
> 11
> & ")"
>
> .Range("E11").Formula = .Range("E11").Formula & _
> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$H$" & Rnum +
> 11
> & ")"
> .Range("E12").Formula = .Range("E12").Formula & _
> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
> 11
> & ")"
>
> mName = mName + 1
> Rnum = 39 + mName
> .Protect
> End With
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationAutomatic
> .ScreenUpdating = True
> End With
>
> MsgBox "Done"
>
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:urpY3ZBjFHA.3784@tk2msftngp13.phx.gbl...
>> ok
>>
>> Option Explicit
>> Dim uName As String
>> Dim rNum As Integer
>> Dim mName As Integer
>> Dim cnt As Integer
>> Dim Counter As Integer
>>
>> Sub Add_User()
>>
>> Application.ScreenUpdating = False
>>
>> Counter = 1
>> cnt = 1
>> mName = 4
>> uName = "New Name Here" ' enter new person's name (workbook name)
>> rNum = 39 + mName ' starting row for data in new sheet (43)
>>
>>
>> Do
>> For Counter = 1 To 12 Step 1
>> Sheets(mName).Select
>> With Sheets(mName)
>> ActiveSheet.Unprotect
>> .Range("C5").Formula = .Range("c5").Formula & "+[" & uName &
>> ".xls]Branch!$C$" & rNum & ""
>> .Range("C6").Formula = .Range("c6").Formula & "+[" & uName &
>> ".xls]Branch!$F$" & rNum & ""
>>
>> .Range("D5").Formula = .Range("D5").Formula & "+[" & uName &
>> ".xls]Branch!$D$" & rNum & ""
>> .Range("D6").Formula = .Range("D6").Formula & "+[" & uName &
>> ".xls]Branch!$G$" & rNum & ""
>>
>> .Range("E5").Formula = .Range("E5").Formula & "+[" & uName &
>> ".xls]Branch!$H$" & rNum & ""
>> .Range("E6").Formula = .Range("E6").Formula & "+[" & uName &
>> ".xls]Branch!$I$" & rNum & ""
>>
>> rNum = rNum + 14
>> .Range("C7").Formula = .Range("c7").Formula & "+[" & uName &
>> ".xls]Branch!$C$" & rNum & ""
>> .Range("C8").Formula = .Range("c8").Formula & "+[" & uName &
>> ".xls]Branch!$F$" & rNum & ""
>>
>> .Range("D7").Formula = .Range("D7").Formula & "+[" & uName &
>> ".xls]Branch!$D$" & rNum & ""
>> .Range("D8").Formula = .Range("D8").Formula & "+[" & uName &
>> ".xls]Branch!$G$" & rNum & ""
>>
>> .Range("E7").Formula = .Range("E7").Formula & "+[" & uName &
>> ".xls]Branch!$H$" & rNum & ""
>> .Range("E8").Formula = .Range("E8").Formula & "+[" & uName &
>> ".xls]Branch!$I$" & rNum & ""
>>
>> rNum = rNum + 14
>> .Range("C9").Formula = .Range("c9").Formula & "+[" & uName &
>> ".xls]Branch!$C$" & rNum & ""
>> .Range("C10").Formula = .Range("c10").Formula & "+[" & uName &
>> ".xls]Branch!$F$" & rNum & ""
>>
>> .Range("D9").Formula = .Range("D9").Formula & "+[" & uName &
>> ".xls]Branch!$D$" & rNum & ""
>> .Range("D10").Formula = .Range("D10").Formula & "+[" & uName &
>> ".xls]Branch!$G$" & rNum & ""
>>
>> .Range("E9").Formula = .Range("E9").Formula & "+[" & uName &
>> ".xls]Branch!$H$" & rNum & ""
>> .Range("E10").Formula = .Range("E10").Formula & "+[" & uName &
>> ".xls]Branch!$I$" & rNum & ""
>>
>>
>> rNum = rNum + 14
>> .Range("C11").Formula = .Range("c11").Formula & "+[" & uName &
>> ".xls]Branch!$C$" & rNum & ""
>> .Range("C12").Formula = .Range("c12").Formula & "+[" & uName &
>> ".xls]Branch!$F$" & rNum & ""
>>
>> .Range("D11").Formula = .Range("D11").Formula & "+[" & uName &
>> ".xls]Branch!$D$" & rNum & ""
>> .Range("D12").Formula = .Range("D12").Formula & "+[" & uName &
>> ".xls]Branch!$G$" & rNum & ""
>>
>> .Range("E11").Formula = .Range("E11").Formula & "+[" & uName &
>> ".xls]Branch!$H$" & rNum & ""
>> .Range("E12").Formula = .Range("E12").Formula & "+[" & uName &
>> ".xls]Branch!$I$" & rNum & ""
>>
>>
>> End With
>> mName = mName + 1
>> rNum = 39 + mName
>> ActiveSheet.Protect
>> Next Counter
>> Exit Do
>> Loop
>> Application.ScreenUpdating = True
>> MsgBox "Done"
>> End Sub
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
>> news:OT3VbVBjFHA.2444@tk2msftngp13.phx.gbl...
>> > Don't post attachments: no-one will read them. Paste the code into the
>> > message body - work on line breaks as required.
>> >
>> > Tim
>> >
>> > "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
>> > news:%23A7fPKBjFHA.1416@TK2MSFTNGP09.phx.gbl...
>> >> ok, here it is. i used an attachment to help with line breaks
>> >>
>> >> --
>> >>
>> >>
>> >> Gary
>> >>
>> >>
>> >> "Gareth" <msng@garhooREMOVEME.com> wrote in message
>> >> news:O95zlGBjFHA.1480@TK2MSFTNGP10.phx.gbl...
>> >>> Go ahead and post it. I'm sure someone will be able to take it on.
>> >>>
>> >>> Gary Keramidas wrote:
>> >>>> i have a routine i wrote to add a range to an exiting formula. it
>> >>>> has
>> >>>> 24
>> >>>> formulas on 12 sheets hat need to be updated. the linked cells are
>> >>>> an
>> >>>> external workbook with a sheet called branch.
>> >>>>
>> >>>> the routine takes about a minute and a half to execute, but it will
>> >>>> only
>> >>>> execute when a new person is added, couple times a year.
>> >>>>
>> >>>> it is about 50 lines including the dims. is it appropriate to post
>> >>>> it
>> >>>> so i
>> >>>> can ask someone if there is a more efficient way?
>> >>>>
>> >>>> thanks
>> >>>>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Gary,

So where does a minute come from?

Bob

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:%23SHDWQHjFHA.1148@TK2MSFTNGP12.phx.gbl...
> it takes about a second here with blank worksheets. i have an athlon64
3200
> with a gb of ram.
>
> --
>
>
> Gary
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:uZBQwpDjFHA.1480@TK2MSFTNGP10.phx.gbl...
> > BTW, it took about 1 sec on my machine, what do you have?
> >
> > --
> > HTH
> >
> > Bob Phillips

i guess because each formula is over 700 characters long to begin with. my
original routine must be slowed by that, i guess

--


Gary


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:eOEEPWHjFHA.476@TK2MSFTNGP14.phx.gbl...
> Gary,
>
> So where does a minute come from?
>
> Bob
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:%23SHDWQHjFHA.1148@TK2MSFTNGP12.phx.gbl...
>> it takes about a second here with blank worksheets. i have an athlon64
> 3200
>> with a gb of ram.
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
>> news:uZBQwpDjFHA.1480@TK2MSFTNGP10.phx.gbl...
>> > BTW, it took about 1 sec on my machine, what do you have?
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>
>

Did my loopless code help at all?

--
HTH

Bob Phillips

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:%23h5jUbHjFHA.1416@TK2MSFTNGP09.phx.gbl...
> i guess because each formula is over 700 characters long to begin with.
my
> original routine must be slowed by that, i guess
>
> --
>
>
> Gary
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:eOEEPWHjFHA.476@TK2MSFTNGP14.phx.gbl...
> > Gary,
> >
> > So where does a minute come from?
> >
> > Bob
> >
> > "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> > news:%23SHDWQHjFHA.1148@TK2MSFTNGP12.phx.gbl...
> >> it takes about a second here with blank worksheets. i have an athlon64
> > 3200
> >> with a gb of ram.
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> >> news:uZBQwpDjFHA.1480@TK2MSFTNGP10.phx.gbl...
> >> > BTW, it took about 1 sec on my machine, what do you have?
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >
> >
>
>

ok, i made the changes to the formula and it works. but i noticed, since you
removed my loop, it only made changes to the january sheet. i need the loop
to modify feb through dec. too. after adding the loop back, it still took a
minute and a half to complete.

there are actually 24 formulas on 12 sheets that need to be modified. each
sheet appears to take about 8 seconds to modify.


thanks for your time and work
--


Gary


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:%23k5aMzDjFHA.3912@TK2MSFTNGP10.phx.gbl...
> Looked at it some more and the loop is also unnecessary. Just use an Excel
> formula that sums
>
> Sub Add_User()
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> With ActiveSheet
> PgBreakMode = .DisplayPageBreaks
> .DisplayPageBreaks = False
> End With
>
> cnt = 1
> mName = 4
> uName = "Bob" ' enter new person's name (workbook name)
> Rnum = 39 + mName ' starting row for data in new sheet (43)
>
> With Sheets(mName)
> .Unprotect
> .Range("C5").Formula = .Range("C5").Formula & _
> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
> 11
> & ")"
> .Range("C6").Formula = .Range("C6").Formula & _
> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
> 11
> & ")"
>
> .Range("D5").Formula = .Range("D5").Formula & _
> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
> 11
> & ")"
> .Range("D6").Formula = .Range("D6").Formula & _
> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
> 11
> & ")"
>
> .Range("E5").Formula = .Range("E5").Formula & _
> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
> 11
> & ")"
> .Range("E6").Formula = .Range("E6").Formula & _
> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
> 11
> & ")"
>
> Rnum = Rnum + 14
> .Range("C7").Formula = .Range("c7").Formula & _
> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
> 11
> & ")"
> .Range("C8").Formula = .Range("c8").Formula & _
> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
> 11
> & ")"
>
> .Range("D7").Formula = .Range("D7").Formula & _
> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
> 11
> & ")"
> .Range("D8").Formula = .Range("D8").Formula & _
> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
> 11
> & ")"
>
> .Range("E7").Formula = .Range("E7").Formula & _
> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
> 11
> & ")"
> .Range("E8").Formula = .Range("E8").Formula & _
> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
> 11
> & ")"
>
> Rnum = Rnum + 14
> .Range("C9").Formula = .Range("c9").Formula & _
> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
> 11
> & ")"
> .Range("C10").Formula = .Range("c10").Formula & _
> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
> 11
> & ")"
>
> .Range("D9").Formula = .Range("D9").Formula & _
> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
> 11
> & ")"
> .Range("D10").Formula = .Range("D10").Formula & _
> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
> 11
> & ")"
>
> .Range("E9").Formula = .Range("E9").Formula & _
> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
> 11
> & ")"
> .Range("E10").Formula = .Range("E10").Formula & _
> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
> 11
> & ")"
>
> Rnum = Rnum + 14
> .Range("C11").Formula = .Range("C11").Formula & _
> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
> 11
> & ")"
> .Range("C12").Formula = .Range("C12").Formula & _
> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
> 11
> & ")"
>
> .Range("D11").Formula = .Range("D11").Formula & _
> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
> 11
> & ")"
> .Range("D12").Formula = .Range("D12").Formula & _
> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
> 11
> & ")"
>
> .Range("E11").Formula = .Range("E11").Formula & _
> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$H$" & Rnum +
> 11
> & ")"
> .Range("E12").Formula = .Range("E12").Formula & _
> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
> 11
> & ")"
>
> mName = mName + 1
> Rnum = 39 + mName
> .Protect
> End With
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationAutomatic
> .ScreenUpdating = True
> End With
>
> MsgBox "Done"
>
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:urpY3ZBjFHA.3784@tk2msftngp13.phx.gbl...
>> ok
>>
>> Option Explicit
>> Dim uName As String
>> Dim rNum As Integer
>> Dim mName As Integer
>> Dim cnt As Integer
>> Dim Counter As Integer
>>
>> Sub Add_User()
>>
>> Application.ScreenUpdating = False
>>
>> Counter = 1
>> cnt = 1
>> mName = 4
>> uName = "New Name Here" ' enter new person's name (workbook name)
>> rNum = 39 + mName ' starting row for data in new sheet (43)
>>
>>
>> Do
>> For Counter = 1 To 12 Step 1
>> Sheets(mName).Select
>> With Sheets(mName)
>> ActiveSheet.Unprotect
>> .Range("C5").Formula = .Range("c5").Formula & "+[" & uName &
>> ".xls]Branch!$C$" & rNum & ""
>> .Range("C6").Formula = .Range("c6").Formula & "+[" & uName &
>> ".xls]Branch!$F$" & rNum & ""
>>
>> .Range("D5").Formula = .Range("D5").Formula & "+[" & uName &
>> ".xls]Branch!$D$" & rNum & ""
>> .Range("D6").Formula = .Range("D6").Formula & "+[" & uName &
>> ".xls]Branch!$G$" & rNum & ""
>>
>> .Range("E5").Formula = .Range("E5").Formula & "+[" & uName &
>> ".xls]Branch!$H$" & rNum & ""
>> .Range("E6").Formula = .Range("E6").Formula & "+[" & uName &
>> ".xls]Branch!$I$" & rNum & ""
>>
>> rNum = rNum + 14
>> .Range("C7").Formula = .Range("c7").Formula & "+[" & uName &
>> ".xls]Branch!$C$" & rNum & ""
>> .Range("C8").Formula = .Range("c8").Formula & "+[" & uName &
>> ".xls]Branch!$F$" & rNum & ""
>>
>> .Range("D7").Formula = .Range("D7").Formula & "+[" & uName &
>> ".xls]Branch!$D$" & rNum & ""
>> .Range("D8").Formula = .Range("D8").Formula & "+[" & uName &
>> ".xls]Branch!$G$" & rNum & ""
>>
>> .Range("E7").Formula = .Range("E7").Formula & "+[" & uName &
>> ".xls]Branch!$H$" & rNum & ""
>> .Range("E8").Formula = .Range("E8").Formula & "+[" & uName &
>> ".xls]Branch!$I$" & rNum & ""
>>
>> rNum = rNum + 14
>> .Range("C9").Formula = .Range("c9").Formula & "+[" & uName &
>> ".xls]Branch!$C$" & rNum & ""
>> .Range("C10").Formula = .Range("c10").Formula & "+[" & uName &
>> ".xls]Branch!$F$" & rNum & ""
>>
>> .Range("D9").Formula = .Range("D9").Formula & "+[" & uName &
>> ".xls]Branch!$D$" & rNum & ""
>> .Range("D10").Formula = .Range("D10").Formula & "+[" & uName &
>> ".xls]Branch!$G$" & rNum & ""
>>
>> .Range("E9").Formula = .Range("E9").Formula & "+[" & uName &
>> ".xls]Branch!$H$" & rNum & ""
>> .Range("E10").Formula = .Range("E10").Formula & "+[" & uName &
>> ".xls]Branch!$I$" & rNum & ""
>>
>>
>> rNum = rNum + 14
>> .Range("C11").Formula = .Range("c11").Formula & "+[" & uName &
>> ".xls]Branch!$C$" & rNum & ""
>> .Range("C12").Formula = .Range("c12").Formula & "+[" & uName &
>> ".xls]Branch!$F$" & rNum & ""
>>
>> .Range("D11").Formula = .Range("D11").Formula & "+[" & uName &
>> ".xls]Branch!$D$" & rNum & ""
>> .Range("D12").Formula = .Range("D12").Formula & "+[" & uName &
>> ".xls]Branch!$G$" & rNum & ""
>>
>> .Range("E11").Formula = .Range("E11").Formula & "+[" & uName &
>> ".xls]Branch!$H$" & rNum & ""
>> .Range("E12").Formula = .Range("E12").Formula & "+[" & uName &
>> ".xls]Branch!$I$" & rNum & ""
>>
>>
>> End With
>> mName = mName + 1
>> rNum = 39 + mName
>> ActiveSheet.Protect
>> Next Counter
>> Exit Do
>> Loop
>> Application.ScreenUpdating = True
>> MsgBox "Done"
>> End Sub
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
>> news:OT3VbVBjFHA.2444@tk2msftngp13.phx.gbl...
>> > Don't post attachments: no-one will read them. Paste the code into the
>> > message body - work on line breaks as required.
>> >
>> > Tim
>> >
>> > "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
>> > news:%23A7fPKBjFHA.1416@TK2MSFTNGP09.phx.gbl...
>> >> ok, here it is. i used an attachment to help with line breaks
>> >>
>> >> --
>> >>
>> >>
>> >> Gary
>> >>
>> >>
>> >> "Gareth" <msng@garhooREMOVEME.com> wrote in message
>> >> news:O95zlGBjFHA.1480@TK2MSFTNGP10.phx.gbl...
>> >>> Go ahead and post it. I'm sure someone will be able to take it on.
>> >>>
>> >>> Gary Keramidas wrote:
>> >>>> i have a routine i wrote to add a range to an exiting formula. it
>> >>>> has
>> >>>> 24
>> >>>> formulas on 12 sheets hat need to be updated. the linked cells are
>> >>>> an
>> >>>> external workbook with a sheet called branch.
>> >>>>
>> >>>> the routine takes about a minute and a half to execute, but it will
>> >>>> only
>> >>>> execute when a new person is added, couple times a year.
>> >>>>
>> >>>> it is about 50 lines including the dims. is it appropriate to post
>> >>>> it
>> >>>> so i
>> >>>> can ask someone if there is a more efficient way?
>> >>>>
>> >>>> thanks
>> >>>>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Hi Gary,

Given that you run this procedure only a couple of times per year and that,
with the 'bad' version taking 1.5 minutes to run, this equtes to 3 minutes
per year, are you really that bothered - apart of course from the
perspectives of efficient code practice and personal satisfaction?

---
Regards,
Norman



"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:eHBBfjHjFHA.1044@tk2msftngp13.phx.gbl...
> ok, i made the changes to the formula and it works. but i noticed, since
> you removed my loop, it only made changes to the january sheet. i need the
> loop to modify feb through dec. too. after adding the loop back, it still
> took a minute and a half to complete.
>
> there are actually 24 formulas on 12 sheets that need to be modified. each
> sheet appears to take about 8 seconds to modify.
>
>
> thanks for your time and work
> --
>
>
> Gary
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:%23k5aMzDjFHA.3912@TK2MSFTNGP10.phx.gbl...
>> Looked at it some more and the loop is also unnecessary. Just use an
>> Excel
>> formula that sums
>>
>> Sub Add_User()
>>
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> With ActiveSheet
>> PgBreakMode = .DisplayPageBreaks
>> .DisplayPageBreaks = False
>> End With
>>
>> cnt = 1
>> mName = 4
>> uName = "Bob" ' enter new person's name (workbook name)
>> Rnum = 39 + mName ' starting row for data in new sheet (43)
>>
>> With Sheets(mName)
>> .Unprotect
>> .Range("C5").Formula = .Range("C5").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
>> 11
>> & ")"
>> .Range("C6").Formula = .Range("C6").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
>> 11
>> & ")"
>>
>> .Range("D5").Formula = .Range("D5").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
>> 11
>> & ")"
>> .Range("D6").Formula = .Range("D6").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
>> 11
>> & ")"
>>
>> .Range("E5").Formula = .Range("E5").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
>> 11
>> & ")"
>> .Range("E6").Formula = .Range("E6").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
>> 11
>> & ")"
>>
>> Rnum = Rnum + 14
>> .Range("C7").Formula = .Range("c7").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
>> 11
>> & ")"
>> .Range("C8").Formula = .Range("c8").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
>> 11
>> & ")"
>>
>> .Range("D7").Formula = .Range("D7").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
>> 11
>> & ")"
>> .Range("D8").Formula = .Range("D8").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
>> 11
>> & ")"
>>
>> .Range("E7").Formula = .Range("E7").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
>> 11
>> & ")"
>> .Range("E8").Formula = .Range("E8").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
>> 11
>> & ")"
>>
>> Rnum = Rnum + 14
>> .Range("C9").Formula = .Range("c9").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
>> 11
>> & ")"
>> .Range("C10").Formula = .Range("c10").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
>> 11
>> & ")"
>>
>> .Range("D9").Formula = .Range("D9").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
>> 11
>> & ")"
>> .Range("D10").Formula = .Range("D10").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
>> 11
>> & ")"
>>
>> .Range("E9").Formula = .Range("E9").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$E$" & Rnum +
>> 11
>> & ")"
>> .Range("E10").Formula = .Range("E10").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
>> 11
>> & ")"
>>
>> Rnum = Rnum + 14
>> .Range("C11").Formula = .Range("C11").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$C$" & Rnum & ":$C$" & Rnum +
>> 11
>> & ")"
>> .Range("C12").Formula = .Range("C12").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$F$" & Rnum & ":$F$" & Rnum +
>> 11
>> & ")"
>>
>> .Range("D11").Formula = .Range("D11").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$D$" & Rnum & ":$D$" & Rnum +
>> 11
>> & ")"
>> .Range("D12").Formula = .Range("D12").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$G$" & Rnum & ":$G$" & Rnum +
>> 11
>> & ")"
>>
>> .Range("E11").Formula = .Range("E11").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$H$" & Rnum & ":$H$" & Rnum +
>> 11
>> & ")"
>> .Range("E12").Formula = .Range("E12").Formula & _
>> "+SUM([" & uName & ".xls]Branch!$I$" & Rnum & ":$I$" & Rnum +
>> 11
>> & ")"
>>
>> mName = mName + 1
>> Rnum = 39 + mName
>> .Protect
>> End With
>>
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationAutomatic
>> .ScreenUpdating = True
>> End With
>>
>> MsgBox "Done"
>>
>> End Sub
>>
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
>> news:urpY3ZBjFHA.3784@tk2msftngp13.phx.gbl...
>>> ok
>>>
>>> Option Explicit
>>> Dim uName As String
>>> Dim rNum As Integer
>>> Dim mName As Integer
>>> Dim cnt As Integer
>>> Dim Counter As Integer
>>>
>>> Sub Add_User()
>>>
>>> Application.ScreenUpdating = False
>>>
>>> Counter = 1
>>> cnt = 1
>>> mName = 4
>>> uName = "New Name Here" ' enter new person's name (workbook name)
>>> rNum = 39 + mName ' starting row for data in new sheet (43)
>>>
>>>
>>> Do
>>> For Counter = 1 To 12 Step 1
>>> Sheets(mName).Select
>>> With Sheets(mName)
>>> ActiveSheet.Unprotect
>>> .Range("C5").Formula = .Range("c5").Formula & "+[" & uName &
>>> ".xls]Branch!$C$" & rNum & ""
>>> .Range("C6").Formula = .Range("c6").Formula & "+[" & uName &
>>> ".xls]Branch!$F$" & rNum & ""
>>>
>>> .Range("D5").Formula = .Range("D5").Formula & "+[" & uName &
>>> ".xls]Branch!$D$" & rNum & ""
>>> .Range("D6").Formula = .Range("D6").Formula & "+[" & uName &
>>> ".xls]Branch!$G$" & rNum & ""
>>>
>>> .Range("E5").Formula = .Range("E5").Formula & "+[" & uName &
>>> ".xls]Branch!$H$" & rNum & ""
>>> .Range("E6").Formula = .Range("E6").Formula & "+[" & uName &
>>> ".xls]Branch!$I$" & rNum & ""
>>>
>>> rNum = rNum + 14
>>> .Range("C7").Formula = .Range("c7").Formula & "+[" & uName &
>>> ".xls]Branch!$C$" & rNum & ""
>>> .Range("C8").Formula = .Range("c8").Formula & "+[" & uName &
>>> ".xls]Branch!$F$" & rNum & ""
>>>
>>> .Range("D7").Formula = .Range("D7").Formula & "+[" & uName &
>>> ".xls]Branch!$D$" & rNum & ""
>>> .Range("D8").Formula = .Range("D8").Formula & "+[" & uName &
>>> ".xls]Branch!$G$" & rNum & ""
>>>
>>> .Range("E7").Formula = .Range("E7").Formula & "+[" & uName &
>>> ".xls]Branch!$H$" & rNum & ""
>>> .Range("E8").Formula = .Range("E8").Formula & "+[" & uName &
>>> ".xls]Branch!$I$" & rNum & ""
>>>
>>> rNum = rNum + 14
>>> .Range("C9").Formula = .Range("c9").Formula & "+[" & uName &
>>> ".xls]Branch!$C$" & rNum & ""
>>> .Range("C10").Formula = .Range("c10").Formula & "+[" & uName &
>>> ".xls]Branch!$F$" & rNum & ""
>>>
>>> .Range("D9").Formula = .Range("D9").Formula & "+[" & uName &
>>> ".xls]Branch!$D$" & rNum & ""
>>> .Range("D10").Formula = .Range("D10").Formula & "+[" & uName &
>>> ".xls]Branch!$G$" & rNum & ""
>>>
>>> .Range("E9").Formula = .Range("E9").Formula & "+[" & uName &
>>> ".xls]Branch!$H$" & rNum & ""
>>> .Range("E10").Formula = .Range("E10").Formula & "+[" & uName &
>>> ".xls]Branch!$I$" & rNum & ""
>>>
>>>
>>> rNum = rNum + 14
>>> .Range("C11").Formula = .Range("c11").Formula & "+[" & uName &
>>> ".xls]Branch!$C$" & rNum & ""
>>> .Range("C12").Formula = .Range("c12").Formula & "+[" & uName &
>>> ".xls]Branch!$F$" & rNum & ""
>>>
>>> .Range("D11").Formula = .Range("D11").Formula & "+[" & uName &
>>> ".xls]Branch!$D$" & rNum & ""
>>> .Range("D12").Formula = .Range("D12").Formula & "+[" & uName &
>>> ".xls]Branch!$G$" & rNum & ""
>>>
>>> .Range("E11").Formula = .Range("E11").Formula & "+[" & uName &
>>> ".xls]Branch!$H$" & rNum & ""
>>> .Range("E12").Formula = .Range("E12").Formula & "+[" & uName &
>>> ".xls]Branch!$I$" & rNum & ""
>>>
>>>
>>> End With
>>> mName = mName + 1
>>> rNum = 39 + mName
>>> ActiveSheet.Protect
>>> Next Counter
>>> Exit Do
>>> Loop
>>> Application.ScreenUpdating = True
>>> MsgBox "Done"
>>> End Sub
>>>
>>> --
>>>
>>>
>>> Gary
>>>
>>>
>>> "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message
>>> news:OT3VbVBjFHA.2444@tk2msftngp13.phx.gbl...
>>> > Don't post attachments: no-one will read them. Paste the code into the
>>> > message body - work on line breaks as required.
>>> >
>>> > Tim
>>> >
>>> > "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
>>> > news:%23A7fPKBjFHA.1416@TK2MSFTNGP09.phx.gbl...
>>> >> ok, here it is. i used an attachment to help with line breaks
>>> >>
>>> >> --
>>> >>
>>> >>
>>> >> Gary
>>> >>
>>> >>
>>> >> "Gareth" <msng@garhooREMOVEME.com> wrote in message
>>> >> news:O95zlGBjFHA.1480@TK2MSFTNGP10.phx.gbl...
>>> >>> Go ahead and post it. I'm sure someone will be able to take it on.
>>> >>>
>>> >>> Gary Keramidas wrote:
>>> >>>> i have a routine i wrote to add a range to an exiting formula. it
>>> >>>> has
>>> >>>> 24
>>> >>>> formulas on 12 sheets hat need to be updated. the linked cells are
>>> >>>> an
>>> >>>> external workbook with a sheet called branch.
>>> >>>>
>>> >>>> the routine takes about a minute and a half to execute, but it will
>>> >>>> only
>>> >>>> execute when a new person is added, couple times a year.
>>> >>>>
>>> >>>> it is about 50 lines including the dims. is it appropriate to post
>>> >>>> it
>>> >>>> so i
>>> >>>> can ask someone if there is a more efficient way?
>>> >>>>
>>> >>>> thanks
>>> >>>>
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>