In cell V7 of my worksheet I have the following formula:

=SUM(E7/Sheet1!$D$26)*52

What I would like to do is copy this formula down to line V501 without
having to copy and paste. I also have this same formula in 7 other worksheets
so I would also like to do the same in those as well. I am assuming I will
need some kind of loop in order to do it in the one sheet as well as the
others but do not know how to execute it.

Thanks.

Bob



Thanks.


--
Bob

Hi! Bob.
Try placing a Command Button some where in you worksheet, enter in Design
Mode and double clic teh Command Button. In Visual Basic code editor paste
the following code inside de command's button sub.

---------------------------------------------------------------------------------------------
'Variable declaration'
Dim VarRow As Integer
Dim VarSheetName As String

'Variable inicialization'
VarSheetName = Me.Name

'Loop' v501
For VarRow = 7 To 501
Me.Cells(VarRow, 22) = "=SUM(E" & VarRow & "/" & VarSheetName &
"!$D$26)*52"
Next
----------------------------------------------------------------------------------------------

Thanks. Where in this code would I need to use my particular sheet names?

--
Bob


"Lt. Bonifacius" wrote:

> Hi! Bob.
> Try placing a Command Button some where in you worksheet, enter in Design
> Mode and double clic teh Command Button. In Visual Basic code editor paste
> the following code inside de command's button sub.
>
> ---------------------------------------------------------------------------------------------
> 'Variable declaration'
> Dim VarRow As Integer
> Dim VarSheetName As String
>
> 'Variable inicialization'
> VarSheetName = Me.Name
>
> 'Loop' v501
> For VarRow = 7 To 501
> Me.Cells(VarRow, 22) = "=SUM(E" & VarRow & "/" & VarSheetName &
> "!$D$26)*52"
> Next
> ----------------------------------------------------------------------------------------------
>
>

This macro allows you to specify the worksheets (in the Array function call)
and copy the formula to the required cells on each sheet...

Sub CopyCell()
Dim X As Long
Dim Sh As Variant
For Each Sh In Array("Sheet1", "Sheet2", "Sheet3")
For X = 7 To 501
Worksheets(Sh).Range("B" & CStr(X)).Formula = _
"=SUM(E" & CStr(X) & "/" & Sh & "!$D$26)*52"
Next
Next
End Sub


Rick



"Bob" <(E-Mail Removed)> wrote in message
news:18E2AE9E-8776-4B47-ADAC-(E-Mail Removed)...
> In cell V7 of my worksheet I have the following formula:
>
> =SUM(E7/Sheet1!$D$26)*52
>
> What I would like to do is copy this formula down to line V501 without
> having to copy and paste. I also have this same formula in 7 other
> worksheets
> so I would also like to do the same in those as well. I am assuming I will
> need some kind of loop in order to do it in the one sheet as well as the
> others but do not know how to execute it.
>
> Thanks.
>
> Bob
>
>
>
> Thanks.
>
>
> --
> Bob

Just a note.

VBA is pretty forgiving. You can concatenate text with numbers and still end up
with text.

..Range("B" & CStr(X))
could be written as:
..Range("B" & X)

(same in the =sum() portion, too.)

I like:
..cells(x,"B")
though.

I'm guessing that VB is less forgiving?????

"Rick Rothstein (MVP - VB)" wrote:
>
> This macro allows you to specify the worksheets (in the Array function call)
> and copy the formula to the required cells on each sheet...
>
> Sub CopyCell()
> Dim X As Long
> Dim Sh As Variant
> For Each Sh In Array("Sheet1", "Sheet2", "Sheet3")
> For X = 7 To 501
> Worksheets(Sh).Range("B" & CStr(X)).Formula = _
> "=SUM(E" & CStr(X) & "/" & Sh & "!$D$26)*52"
> Next
> Next
> End Sub
>
> Rick
>
> "Bob" <(E-Mail Removed)> wrote in message
> news:18E2AE9E-8776-4B47-ADAC-(E-Mail Removed)...
> > In cell V7 of my worksheet I have the following formula:
> >
> > =SUM(E7/Sheet1!$D$26)*52
> >
> > What I would like to do is copy this formula down to line V501 without
> > having to copy and paste. I also have this same formula in 7 other
> > worksheets
> > so I would also like to do the same in those as well. I am assuming I will
> > need some kind of loop in order to do it in the one sheet as well as the
> > others but do not know how to execute it.
> >
> > Thanks.
> >
> > Bob
> >
> >
> >
> > Thanks.
> >
> >
> > --
> > Bob

--

Dave Peterson

Thanks Rick - my only issue is that my $D$26 reference resides on a different
sheet - named "sheet1". When I ran the macro it pulled the D26 from the sheet
I was running it from. How can I change the cell reference?


--
Bob


"Rick Rothstein (MVP - VB)" wrote:

> This macro allows you to specify the worksheets (in the Array function call)
> and copy the formula to the required cells on each sheet...
>
> Sub CopyCell()
> Dim X As Long
> Dim Sh As Variant
> For Each Sh In Array("Sheet1", "Sheet2", "Sheet3")
> For X = 7 To 501
> Worksheets(Sh).Range("B" & CStr(X)).Formula = _
> "=SUM(E" & CStr(X) & "/" & Sh & "!$D$26)*52"
> Next
> Next
> End Sub
>
>
> Rick
>
>
>
> "Bob" <(E-Mail Removed)> wrote in message
> news:18E2AE9E-8776-4B47-ADAC-(E-Mail Removed)...
> > In cell V7 of my worksheet I have the following formula:
> >
> > =SUM(E7/Sheet1!$D$26)*52
> >
> > What I would like to do is copy this formula down to line V501 without
> > having to copy and paste. I also have this same formula in 7 other
> > worksheets
> > so I would also like to do the same in those as well. I am assuming I will
> > need some kind of loop in order to do it in the one sheet as well as the
> > others but do not know how to execute it.
> >
> > Thanks.
> >
> > Bob
> >
> >
> >
> > Thanks.
> >
> >
> > --
> > Bob
>
>

If I understand you correctly, you want the reference for $D$26 to always be
that cell on Sheet1, no matter what sheet you are copying the formula to. If
that is correct, this should accomplish that for you...

Sub CopyCell()
Dim X As Long
Dim Sh As Variant
For Each Sh In Array("Sheet1", "Sheet2", "Sheet3")
For X = 7 To 501
Worksheets(Sh).Range("B" & CStr(X)).Formula = _
"=SUM(E" & CStr(X) & "/Sheet1!$D$26)*52"
Next
Next
End Sub



"Bob" <(E-Mail Removed)> wrote in message
news:4CBDF49D-6D4E-463B-AB1B-(E-Mail Removed)...
> Thanks Rick - my only issue is that my $D$26 reference resides on a
> different
> sheet - named "sheet1". When I ran the macro it pulled the D26 from the
> sheet
> I was running it from. How can I change the cell reference?
> --
> Bob
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> This macro allows you to specify the worksheets (in the Array function
>> call)
>> and copy the formula to the required cells on each sheet...
>>
>> Sub CopyCell()
>> Dim X As Long
>> Dim Sh As Variant
>> For Each Sh In Array("Sheet1", "Sheet2", "Sheet3")
>> For X = 7 To 501
>> Worksheets(Sh).Range("B" & CStr(X)).Formula = _
>> "=SUM(E" & CStr(X) & "/" & Sh & "!$D$26)*52"
>> Next
>> Next
>> End Sub
>>
>>
>> Rick
>>
>>
>>
>> "Bob" <(E-Mail Removed)> wrote in message
>> news:18E2AE9E-8776-4B47-ADAC-(E-Mail Removed)...
>> > In cell V7 of my worksheet I have the following formula:
>> >
>> > =SUM(E7/Sheet1!$D$26)*52
>> >
>> > What I would like to do is copy this formula down to line V501 without
>> > having to copy and paste. I also have this same formula in 7 other
>> > worksheets
>> > so I would also like to do the same in those as well. I am assuming I
>> > will
>> > need some kind of loop in order to do it in the one sheet as well as
>> > the
>> > others but do not know how to execute it.
>> >
>> > Thanks.
>> >
>> > Bob
>> >
>> >
>> >
>> > Thanks.
>> >
>> >
>> > --
>> > Bob
>>
>>

> Just a note.
>
> VBA is pretty forgiving. You can concatenate text with numbers and still
> end up
> with text.
>
> .Range("B" & CStr(X))
> could be written as:
> .Range("B" & X)
>
> (same in the =sum() portion, too.)
>
> I like:
> .cells(x,"B")
> though.
>
> I'm guessing that VB is less forgiving?????

No, actually, the guts of VBA and compiled VB are the same; so, in this
case, VB would be as forgiving. In the compiled VB world, it is considered
(and please don't take this the wrong way) poor programming practice to let
VB handle the conversions automatically when you know in advance what the
data type should be. Over there, VB's underlying data type coercions are
called "evil type coercions" and are to be avoided whenever possible. In
addition, not omitting default object properties (the Value property of a
Range being an example), expressly declaring variable types and avoiding
Variants whenever possible are also considered good programming practice as
well... three more things which seem to be laxly adhered to in the Excel
community. So, when you see me doing any of these things (such as expressly
using the CStr function as you pointed out), it will simply be a case of
"old habits die hard".

Rick

I agree with you about declarations and not relying on default properties. (I
cringe when I see these violations(?), too.)

But (I think) the cStr() really clutters the code and makes it difficult to
read.

Here's hoping that you come over to the evil side on that part quickly.

ps. One more thing that I cringe when I see--unqualified ranges. That causes
more trouble when code is copy|pasted to a different module/project.

"Rick Rothstein (MVP - VB)" wrote:
>
> > Just a note.
> >
> > VBA is pretty forgiving. You can concatenate text with numbers and still
> > end up
> > with text.
> >
> > .Range("B" & CStr(X))
> > could be written as:
> > .Range("B" & X)
> >
> > (same in the =sum() portion, too.)
> >
> > I like:
> > .cells(x,"B")
> > though.
> >
> > I'm guessing that VB is less forgiving?????
>
> No, actually, the guts of VBA and compiled VB are the same; so, in this
> case, VB would be as forgiving. In the compiled VB world, it is considered
> (and please don't take this the wrong way) poor programming practice to let
> VB handle the conversions automatically when you know in advance what the
> data type should be. Over there, VB's underlying data type coercions are
> called "evil type coercions" and are to be avoided whenever possible. In
> addition, not omitting default object properties (the Value property of a
> Range being an example), expressly declaring variable types and avoiding
> Variants whenever possible are also considered good programming practice as
> well... three more things which seem to be laxly adhered to in the Excel
> community. So, when you see me doing any of these things (such as expressly
> using the CStr function as you pointed out), it will simply be a case of
> "old habits die hard".
>
> Rick

--

Dave Peterson

> Here's hoping that you come over to the evil side on that part quickly.

I'll try... but it just looks so wrong to my "programmer's eye". <g>

> ps. One more thing that I cringe when I see--unqualified ranges. That
> causes
> more trouble when code is copy|pasted to a different module/project.

Noted. I'm guilty of that one, so I'll make a concerted effort to stop doing
that... thanks for pointing it out.

Rick