Range and SUM of 3 cells

  • Thread starter Thread starter ole_
  • Start date Start date
O

ole_

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
 
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?
 
Bob Phillips said:
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?
Click to expand...

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
 
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)
 
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)
 
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
 
Lost me I am afraid.

--

HTH

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