Range and SUM of 3 cells

[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

 

[Bob Phillips]

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("G4141") * 2

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

 

[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("G4141") * 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]

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_]

Is it me or isn't that the same, i am not sure i understand what you mean?

 

[Bob Phillips]

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_]

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]

Lost me I am afraid.

--

HTH

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

 

[ole_]

Okay Bob,

Many thanks for your help.

Ole

Lost me I am afraid.

--

HTH

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