ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Range and SUM of 3 cells (https://www.excelbanter.com/excel-programming/325908-range-sum-3-cells.html)

ole_
Range and SUM of 3 cells
 
Hi Ng,

I have the following code:

""Private Sub CommandButton1_Click()

Dim bClose As Boolean
Dim bk As Workbook
On Error Resume Next
Set bk = Workbooks("prisliste.xls")
On Error GoTo 0
If bk Is Nothing Then
bClose = True
Set bk = Workbooks.Open("C:\prisliste udskrift\prisliste.xls")
End If
bk.Worksheets(1).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Worksheets(2).Range("G41").Value
bk.Save
If bClose Then
bk.Close Savechanges:=False
End If
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True

End Sub""


Now i need to add another cell so it goes something like this:

""Worksheets(2).Range("G38").Value + Worksheets(2).Range("G41").Value +
Worksheets(2).Range("G41").Value""

Cell G41 needs to be + 2 times and it works fine, but i have to do it on G41
to P41
and next time cell G41 needs to be + 3 times (G41 to P41), this goes one up
to 9 times.

And what im thinking is that, thats alot of code and WORK :-) is there not a
easier way to do this ?

Regards,
Ole

Bob Phillips[_6_]
Range and SUM of 3 cells
 
You can use multiplication in VBA

Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 2)

You can also use the worksheet SUM function

bk.Worksheets(1).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Application.SUM("G41:P41") * 2

What do you mean by next time it will be 3?


"ole_" wrote in message
...
Now i need to add another cell so it goes something like this:

""Worksheets(2).Range("G38").Value + Worksheets(2).Range("G41").Value +
Worksheets(2).Range("G41").Value""

Cell G41 needs to be + 2 times and it works fine, but i have to do it on
G41
to P41
and next time cell G41 needs to be + 3 times (G41 to P41), this goes one
up
to 9 times.

And what im thinking is that, thats alot of code and WORK :-) is there not
a
easier way to do this ?

Regards,
Ole

ole_
Range and SUM of 3 cells
 

"Bob Phillips" skrev i en meddelelse
...
You can use multiplication in VBA

Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 2)

You can also use the worksheet SUM function

bk.Worksheets(1).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Application.SUM("G41:P41") * 2

What do you mean by next time it will be 3?


Hi Bob,

Here is (what you helped me with) what i have done:

""bk.Worksheets(1).Range("F11").Value = _
Worksheets(2).Range("F38").Value + Worksheets(2).Range("F41").Value '
kopier 1-4 farver 2 design
bk.Worksheets(1).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Worksheets(2).Range("G41").Value
bk.Worksheets(1).Range("F17").Value = _
Worksheets(2).Range("H38").Value + Worksheets(2).Range("H41").Value
bk.Worksheets(1).Range("F20").Value = _
Worksheets(2).Range("I38").Value + Worksheets(2).Range("I41").Value
bk.Worksheets(1).Range("F23").Value = _
Worksheets(2).Range("J38").Value + Worksheets(2).Range("J41").Value
bk.Worksheets(1).Range("F26").Value = _
Worksheets(2).Range("K38").Value + Worksheets(2).Range("K41").Value
bk.Worksheets(1).Range("F29").Value = _
Worksheets(2).Range("L38").Value + Worksheets(2).Range("L41").Value
bk.Worksheets(1).Range("F32").Value = _
Worksheets(2).Range("M38").Value + Worksheets(2).Range("M41").Value
bk.Worksheets(1).Range("F35").Value = _
Worksheets(2).Range("N38").Value + Worksheets(2).Range("N41").Value
bk.Worksheets(1).Range("F38").Value = _
Worksheets(2).Range("O38").Value + Worksheets(2).Range("O41").Value
bk.Worksheets(1).Range("F41").Value = _
Worksheets(2).Range("P38").Value + Worksheets(2).Range("P41").Value ' 1-4
2 design hertil""

And what i mean by "next time 3" is this:

""bk.Worksheets(1).Range("F11").Value = _
Worksheets(2).Range("F38").Value + Worksheets(2).Range("F41").Value +
Worksheets(2).Range("F41").Value + Worksheets(2).Range("F41").Value""

This goes up to 9 times and first time its copies to row F and next time row
G and so on.
I have tried to do something like this:

""bk.Worksheets(1).Range("F11, F14, F17").Value = _
Worksheets(2).Range("F38, G38, H38").Value + Worksheets(2).Range("F41,
G41, H41").Value ""

And yes i now im a newbee :-)

Do you got a good idea other then the one you just came with, witch is good.

Ole

Bob Phillips[_6_]
Range and SUM of 3 cells
 
Yes put the code in a separate sub and call it with a multiplier parameter

SumData multiplier:=1
.... more code
SumData multiplier:=2
.... more code
SumData multiplier:=3
... more code

etc

Sub SumData(multiplier As Long)
With Worksheets(2)
bk.Worksheets(1).Range("F11").Value = _
.Range("F38").Value + (Range("F41").Value) * multiplier
'kopier 1-4 farver 2 design
bk.Worksheets(1).Range("F14").Value = _
.Range("G38").Value + (.Range("G41").Value) * multiplier
bk.Worksheets(1).Range("F17").Value = _
.Range("H38").Value + (.Range("H41").Value) * multiplier
bk.Worksheets(1).Range("F20").Value = _
.Range("I38").Value + (.Range("I41").Value) * multiplier
bk.Worksheets(1).Range("F23").Value = _
.Range("J38").Value + (.Range("J41").Value) * multiplier
bk.Worksheets(1).Range("F26").Value = _
.Range("K38").Value + (.Range("K41").Value) * multiplier
bk.Worksheets(1).Range("F29").Value = _
.Range("L38").Value + (.Range("L41").Value) * multiplier
bk.Worksheets(1).Range("F32").Value = _
.Range("M38").Value + (.Range("M41").Value) * multiplier
bk.Worksheets(1).Range("F35").Value = _
.Range("N38").Value + (.Range("N41").Value) * multiplier
bk.Worksheets(1).Range("F38").Value = _
.Range("O38").Value + (.Range("O41").Value) * multiplier
bk.Worksheets(1).Range("F41").Value = _
.Range("P38").Value + (.Range("P41").Value) * multiplier
' 1-42 design hertil
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...

"Bob Phillips" skrev i en meddelelse
...
You can use multiplication in VBA

Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value *
2)

You can also use the worksheet SUM function

bk.Worksheets(1).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Application.SUM("G41:P41") * 2

What do you mean by next time it will be 3?


Hi Bob,

snip

And what i mean by "next time 3" is this:

""bk.Worksheets(1).Range("F11").Value = _
Worksheets(2).Range("F38").Value + Worksheets(2).Range("F41").Value +
Worksheets(2).Range("F41").Value + Worksheets(2).Range("F41").Value""

This goes up to 9 times and first time its copies to row F and next time
row
G and so on.
I have tried to do something like this:

""bk.Worksheets(1).Range("F11, F14, F17").Value = _
Worksheets(2).Range("F38, G38, H38").Value + Worksheets(2).Range("F41,
G41, H41").Value ""

And yes i now im a newbee :-)

Do you got a good idea other then the one you just came with, witch is
good.

Ole

ole_
Range and SUM of 3 cells
 
Is it me or isn't that the same, i am not sure i understand what you mean?


"Bob Phillips" skrev i en meddelelse
...
Yes put the code in a separate sub and call it with a multiplier parameter

SumData multiplier:=1
.... more code
SumData multiplier:=2
.... more code
SumData multiplier:=3
... more code

etc

Sub SumData(multiplier As Long)
With Worksheets(2)
bk.Worksheets(1).Range("F11").Value = _
.Range("F38").Value + (Range("F41").Value) * multiplier
'kopier 1-4 farver 2 design
bk.Worksheets(1).Range("F14").Value = _
.Range("G38").Value + (.Range("G41").Value) * multiplier
bk.Worksheets(1).Range("F17").Value = _
.Range("H38").Value + (.Range("H41").Value) * multiplier
bk.Worksheets(1).Range("F20").Value = _
.Range("I38").Value + (.Range("I41").Value) * multiplier
bk.Worksheets(1).Range("F23").Value = _
.Range("J38").Value + (.Range("J41").Value) * multiplier
bk.Worksheets(1).Range("F26").Value = _
.Range("K38").Value + (.Range("K41").Value) * multiplier
bk.Worksheets(1).Range("F29").Value = _
.Range("L38").Value + (.Range("L41").Value) * multiplier
bk.Worksheets(1).Range("F32").Value = _
.Range("M38").Value + (.Range("M41").Value) * multiplier
bk.Worksheets(1).Range("F35").Value = _
.Range("N38").Value + (.Range("N41").Value) * multiplier
bk.Worksheets(1).Range("F38").Value = _
.Range("O38").Value + (.Range("O41").Value) * multiplier
bk.Worksheets(1).Range("F41").Value = _
.Range("P38").Value + (.Range("P41").Value) * multiplier
' 1-42 design hertil
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...

"Bob Phillips" skrev i en meddelelse
...
You can use multiplication in VBA

Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value *
2)

You can also use the worksheet SUM function

bk.Worksheets(1).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Application.SUM("G41:P41") *
2

What do you mean by next time it will be 3?


Hi Bob,

snip

And what i mean by "next time 3" is this:

""bk.Worksheets(1).Range("F11").Value = _
Worksheets(2).Range("F38").Value + Worksheets(2).Range("F41").Value +
Worksheets(2).Range("F41").Value + Worksheets(2).Range("F41").Value""

This goes up to 9 times and first time its copies to row F and next time
row
G and so on.
I have tried to do something like this:

""bk.Worksheets(1).Range("F11, F14, F17").Value = _
Worksheets(2).Range("F38, G38, H38").Value +
Worksheets(2).Range("F41,
G41, H41").Value ""

And yes i now im a newbee :-)

Do you got a good idea other then the one you just came with, witch is
good.

Ole

Bob Phillips[_6_]
Range and SUM of 3 cells
 
No it isn't the same, it achieves the same result without you having to
replicate the code over and over for the different multipliers (which I
still don't understand how you know when it is twice, when three times
etc.).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...
Is it me or isn't that the same, i am not sure i understand what you mean?


"Bob Phillips" skrev i en meddelelse
...
Yes put the code in a separate sub and call it with a multiplier
parameter

SumData multiplier:=1
.... more code
SumData multiplier:=2
.... more code
SumData multiplier:=3
... more code

etc

Sub SumData(multiplier As Long)
With Worksheets(2)
bk.Worksheets(1).Range("F11").Value = _
.Range("F38").Value + (Range("F41").Value) * multiplier
'kopier 1-4 farver 2 design
bk.Worksheets(1).Range("F14").Value = _
.Range("G38").Value + (.Range("G41").Value) * multiplier
bk.Worksheets(1).Range("F17").Value = _
.Range("H38").Value + (.Range("H41").Value) * multiplier
bk.Worksheets(1).Range("F20").Value = _
.Range("I38").Value + (.Range("I41").Value) * multiplier
bk.Worksheets(1).Range("F23").Value = _
.Range("J38").Value + (.Range("J41").Value) * multiplier
bk.Worksheets(1).Range("F26").Value = _
.Range("K38").Value + (.Range("K41").Value) * multiplier
bk.Worksheets(1).Range("F29").Value = _
.Range("L38").Value + (.Range("L41").Value) * multiplier
bk.Worksheets(1).Range("F32").Value = _
.Range("M38").Value + (.Range("M41").Value) * multiplier
bk.Worksheets(1).Range("F35").Value = _
.Range("N38").Value + (.Range("N41").Value) * multiplier
bk.Worksheets(1).Range("F38").Value = _
.Range("O38").Value + (.Range("O41").Value) * multiplier
bk.Worksheets(1).Range("F41").Value = _
.Range("P38").Value + (.Range("P41").Value) * multiplier
' 1-42 design hertil
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...

"Bob Phillips" skrev i en
meddelelse
...
You can use multiplication in VBA

Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value
*
2)

You can also use the worksheet SUM function

bk.Worksheets(1).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Application.SUM("G41:P41")
*
2

What do you mean by next time it will be 3?


Hi Bob,

snip

And what i mean by "next time 3" is this:

""bk.Worksheets(1).Range("F11").Value = _
Worksheets(2).Range("F38").Value + Worksheets(2).Range("F41").Value
+
Worksheets(2).Range("F41").Value + Worksheets(2).Range("F41").Value""

This goes up to 9 times and first time its copies to row F and next
time
row
G and so on.
I have tried to do something like this:

""bk.Worksheets(1).Range("F11, F14, F17").Value = _
Worksheets(2).Range("F38, G38, H38").Value +
Worksheets(2).Range("F41,
G41, H41").Value ""

And yes i now im a newbee :-)

Do you got a good idea other then the one you just came with, witch is
good.

Ole

ole_
Range and SUM of 3 cells
 
Okay, when i multipli twice its copied to row F, when i multipli three times
its copied to Row G
and so on, and i need them all, when i run this macro Row F to Row P and
cell *11, *14, *17, *20, *23, *26
*29, *32, *35, *38, *41 is all filled out.
Can i still use the "SumData multiplier" ?

Ole


"Bob Phillips" skrev i en meddelelse
...
No it isn't the same, it achieves the same result without you having to
replicate the code over and over for the different multipliers (which I
still don't understand how you know when it is twice, when three times
etc.).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...
Is it me or isn't that the same, i am not sure i understand what you
mean?


"Bob Phillips" skrev i en meddelelse
...
Yes put the code in a separate sub and call it with a multiplier
parameter

SumData multiplier:=1
.... more code
SumData multiplier:=2
.... more code
SumData multiplier:=3
... more code

etc

Sub SumData(multiplier As Long)
With Worksheets(2)
bk.Worksheets(1).Range("F11").Value = _
.Range("F38").Value + (Range("F41").Value) * multiplier
'kopier 1-4 farver 2 design
bk.Worksheets(1).Range("F14").Value = _
.Range("G38").Value + (.Range("G41").Value) * multiplier
bk.Worksheets(1).Range("F17").Value = _
.Range("H38").Value + (.Range("H41").Value) * multiplier
bk.Worksheets(1).Range("F20").Value = _
.Range("I38").Value + (.Range("I41").Value) * multiplier
bk.Worksheets(1).Range("F23").Value = _
.Range("J38").Value + (.Range("J41").Value) * multiplier
bk.Worksheets(1).Range("F26").Value = _
.Range("K38").Value + (.Range("K41").Value) * multiplier
bk.Worksheets(1).Range("F29").Value = _
.Range("L38").Value + (.Range("L41").Value) * multiplier
bk.Worksheets(1).Range("F32").Value = _
.Range("M38").Value + (.Range("M41").Value) * multiplier
bk.Worksheets(1).Range("F35").Value = _
.Range("N38").Value + (.Range("N41").Value) * multiplier
bk.Worksheets(1).Range("F38").Value = _
.Range("O38").Value + (.Range("O41").Value) * multiplier
bk.Worksheets(1).Range("F41").Value = _
.Range("P38").Value + (.Range("P41").Value) * multiplier
' 1-42 design hertil
End With
End Sub

Bob Phillips[_6_]
Range and SUM of 3 cells
 
Lost me I am afraid.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...
Okay, when i multipli twice its copied to row F, when i multipli three
times
its copied to Row G
and so on, and i need them all, when i run this macro Row F to Row P and
cell *11, *14, *17, *20, *23, *26
*29, *32, *35, *38, *41 is all filled out.
Can i still use the "SumData multiplier" ?

Ole


"Bob Phillips" skrev i en meddelelse
...
No it isn't the same, it achieves the same result without you having to
replicate the code over and over for the different multipliers (which I
still don't understand how you know when it is twice, when three times
etc.).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...
Is it me or isn't that the same, i am not sure i understand what you
mean?


"Bob Phillips" skrev i en
meddelelse
...
Yes put the code in a separate sub and call it with a multiplier
parameter

SumData multiplier:=1
.... more code
SumData multiplier:=2
.... more code
SumData multiplier:=3
... more code

etc

Sub SumData(multiplier As Long)
With Worksheets(2)
bk.Worksheets(1).Range("F11").Value = _
.Range("F38").Value + (Range("F41").Value) * multiplier
'kopier 1-4 farver 2 design
bk.Worksheets(1).Range("F14").Value = _
.Range("G38").Value + (.Range("G41").Value) * multiplier
bk.Worksheets(1).Range("F17").Value = _
.Range("H38").Value + (.Range("H41").Value) * multiplier
bk.Worksheets(1).Range("F20").Value = _
.Range("I38").Value + (.Range("I41").Value) * multiplier
bk.Worksheets(1).Range("F23").Value = _
.Range("J38").Value + (.Range("J41").Value) * multiplier
bk.Worksheets(1).Range("F26").Value = _
.Range("K38").Value + (.Range("K41").Value) * multiplier
bk.Worksheets(1).Range("F29").Value = _
.Range("L38").Value + (.Range("L41").Value) * multiplier
bk.Worksheets(1).Range("F32").Value = _
.Range("M38").Value + (.Range("M41").Value) * multiplier
bk.Worksheets(1).Range("F35").Value = _
.Range("N38").Value + (.Range("N41").Value) * multiplier
bk.Worksheets(1).Range("F38").Value = _
.Range("O38").Value + (.Range("O41").Value) * multiplier
bk.Worksheets(1).Range("F41").Value = _
.Range("P38").Value + (.Range("P41").Value) * multiplier
' 1-42 design hertil
End With
End Sub

ole_
Range and SUM of 3 cells
 
Okay Bob,

Many thanks for your help.

Ole


"Bob Phillips" skrev i en meddelelse
...
Lost me I am afraid.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...
Okay, when i multipli twice its copied to row F, when i multipli three
times
its copied to Row G
and so on, and i need them all, when i run this macro Row F to Row P and
cell *11, *14, *17, *20, *23, *26
*29, *32, *35, *38, *41 is all filled out.
Can i still use the "SumData multiplier" ?

Ole


"Bob Phillips" skrev i en meddelelse
...
No it isn't the same, it achieves the same result without you having
to
replicate the code over and over for the different multipliers (which
I
still don't understand how you know when it is twice, when three times
etc.).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ole_" wrote in message
...
Is it me or isn't that the same, i am not sure i understand what you
mean?


"Bob Phillips" skrev i en
meddelelse
...
Yes put the code in a separate sub and call it with a multiplier
parameter

SumData multiplier:=1
.... more code
SumData multiplier:=2
.... more code
SumData multiplier:=3
... more code

etc

Sub SumData(multiplier As Long)
With Worksheets(2)
bk.Worksheets(1).Range("F11").Value = _
.Range("F38").Value + (Range("F41").Value) * multiplier
'kopier 1-4 farver 2 design
bk.Worksheets(1).Range("F14").Value = _
.Range("G38").Value + (.Range("G41").Value) *
multiplier
bk.Worksheets(1).Range("F17").Value = _
.Range("H38").Value + (.Range("H41").Value) *
multiplier
bk.Worksheets(1).Range("F20").Value = _
.Range("I38").Value + (.Range("I41").Value) *
multiplier
bk.Worksheets(1).Range("F23").Value = _
.Range("J38").Value + (.Range("J41").Value) *
multiplier
bk.Worksheets(1).Range("F26").Value = _
.Range("K38").Value + (.Range("K41").Value) *
multiplier
bk.Worksheets(1).Range("F29").Value = _
.Range("L38").Value + (.Range("L41").Value) *
multiplier
bk.Worksheets(1).Range("F32").Value = _
.Range("M38").Value + (.Range("M41").Value) *
multiplier
bk.Worksheets(1).Range("F35").Value = _
.Range("N38").Value + (.Range("N41").Value) *
multiplier
bk.Worksheets(1).Range("F38").Value = _
.Range("O38").Value + (.Range("O41").Value) *
multiplier
bk.Worksheets(1).Range("F41").Value = _
.Range("P38").Value + (.Range("P41").Value) *
multiplier
' 1-42 design hertil
End With
End Sub


All times are GMT +1. The time now is 11:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com