Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Range and SUM of 3 cells
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default 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






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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








Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default 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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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





Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default 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







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
When entering data into a range of cells, select the entire range. Q Excel Discussion (Misc queries) 0 September 26th 07 04:36 AM
how to compute a range of cells based on another range of cells? HAROLD Excel Worksheet Functions 1 December 30th 05 09:32 PM
how to compute a range of cells based on another range of cells? HAROLD Excel Worksheet Functions 2 December 30th 05 07:55 PM
Count cells in one range based on parameters in another range dave roth Excel Worksheet Functions 2 March 29th 05 05:33 PM
Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= statement Kevin Excel Programming 7 October 5th 04 08:11 PM


All times are GMT +1. The time now is 08:27 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement