| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
is this vba code correct or is there a more simple way to do this ?
Hi,
I'am a novice on vba programming and constructed this code: Sheets("staffelberekening").Range("I27") = Application.Sum(Sheets ("toekomst_oud").Columns("P:P")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I28") = Application.Sum(Sheets ("toekomst_oud").Columns("AB:AB")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I29") = Application.Sum(Sheets ("toekomst_oud").Columns("AF:AF")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I30") = Application.Sum(Sheets ("toekomst_oud").Columns("AJ:AJ")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I31") = Application.Sum(Sheets ("toekomst_oud").Columns("AN:AN")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I32") = Application.Sum(Sheets ("toekomst_oud").Columns("AR:AR")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I33") = Application.Sum(Sheets ("toekomst_oud").Columns("AV:AV")) + Range("D12") + Range("D13") This works but it seems cumbersome. Is this the correct way to do this or.... Thanks, Jean-Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200508/1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is this vba code correct or is there a more simple way to do this ?
Jean-Pierre,
Which sheets do the ranges Range("D12") + Range("D13") refer to ? NickHk "Jean-Pierre D via OfficeKB.com" wrote in message ... Hi, I'am a novice on vba programming and constructed this code: Sheets("staffelberekening").Range("I27") = Application.Sum(Sheets ("toekomst_oud").Columns("P:P")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I28") = Application.Sum(Sheets ("toekomst_oud").Columns("AB:AB")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I29") = Application.Sum(Sheets ("toekomst_oud").Columns("AF:AF")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I30") = Application.Sum(Sheets ("toekomst_oud").Columns("AJ:AJ")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I31") = Application.Sum(Sheets ("toekomst_oud").Columns("AN:AN")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I32") = Application.Sum(Sheets ("toekomst_oud").Columns("AR:AR")) + Range("D12") + Range("D13") Sheets("staffelberekening").Range("I33") = Application.Sum(Sheets ("toekomst_oud").Columns("AV:AV")) + Range("D12") + Range("D13") This works but it seems cumbersome. Is this the correct way to do this or.... Thanks, Jean-Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200508/1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is this vba code correct or is there a more simple way to do this ?
Hi NickHK,
the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening") The code is in the object sheet1(staffelberekening) under workbook 'selection change' NickHK wrote: Jean-Pierre, Which sheets do the ranges Range("D12") + Range("D13") refer to ? NickHk Hi, [quoted text clipped - 18 lines] Thanks, Jean-Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200508/1 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is this vba code correct or is there a more simple way to do this ?
Jean-Pierre,
Something like: Dim i As Long With Sheets("staffelberekening") .Range("I27").Value = Application.WorksheetFunction.Sum(Sheets("toekomst _oud").Columns("P:P")) + ..Range("D12") + .Range("D13") For i = 0 To 5 .Range("I27").Offset(i, 0).Value = Application.WorksheetFunction.Sum(Sheets("toekomst _oud").Columns(28 + i * 4)) + .Range("D12") + .Range("D13") Next End With I assume the jump from Column "P" to "AB" is intentional. NickHK "Jean-Pierre D via OfficeKB.com" wrote in message ... Hi NickHK, the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening") The code is in the object sheet1(staffelberekening) under workbook 'selection change' NickHK wrote: Jean-Pierre, Which sheets do the ranges Range("D12") + Range("D13") refer to ? NickHk Hi, [quoted text clipped - 18 lines] Thanks, Jean-Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200508/1 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is this vba code correct or is there a more simple way to do this ?
Then it can be simpler
Dim oToe As Worksheet Dim nValue As Double Set oToe = Worksheets("toekomst_oud") With Me nValue = .Range("D12") + .Range("D13") .Range("I27") = oToe.Columns("P:P") + ovalue .Range("I28") = oToe.Columns("AB:AB") + ovalue .Range("I29") = oToe.Columns("AF:AF") + ovalue .Range("I30") = oToe.Columns("AJ:AJ") + ovalue .Range("I31") = oToe.Columns("AN:AN") + ovalue .Range("I32") = oToe.Columns("AR:AR") + ovalue .Range("I33") = oToe.Columns("AV:AV") + ovalue End With -- HTH RP (remove nothere from the email address if mailing direct) "Jean-Pierre D via OfficeKB.com" wrote in message ... Hi NickHK, the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening") The code is in the object sheet1(staffelberekening) under workbook 'selection change' NickHK wrote: Jean-Pierre, Which sheets do the ranges Range("D12") + Range("D13") refer to ? NickHk Hi, [quoted text clipped - 18 lines] Thanks, Jean-Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200508/1 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is this vba code correct or is there a more simple way to do this ?
Seeing Niek's, this too should be
Dim oToe As Worksheet Dim nValue As Double Dim i As Long Set oToe = Worksheets("toekomst_oud") With Me nValue = .Range("D12") + .Range("D13") .Range("I27") = oToe.Columns("P:P") + ovalue For i = 1 To 6 .Range("I" & 27 + i) = oToe.Columns(24 + i * 4)) + ovalue Next i End With -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Then it can be simpler Dim oToe As Worksheet Dim nValue As Double Set oToe = Worksheets("toekomst_oud") With Me nValue = .Range("D12") + .Range("D13") .Range("I27") = oToe.Columns("P:P") + ovalue .Range("I28") = oToe.Columns("AB:AB") + ovalue .Range("I29") = oToe.Columns("AF:AF") + ovalue .Range("I30") = oToe.Columns("AJ:AJ") + ovalue .Range("I31") = oToe.Columns("AN:AN") + ovalue .Range("I32") = oToe.Columns("AR:AR") + ovalue .Range("I33") = oToe.Columns("AV:AV") + ovalue End With -- HTH RP (remove nothere from the email address if mailing direct) "Jean-Pierre D via OfficeKB.com" wrote in message ... Hi NickHK, the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening") The code is in the object sheet1(staffelberekening) under workbook 'selection change' NickHK wrote: Jean-Pierre, Which sheets do the ranges Range("D12") + Range("D13") refer to ? NickHk Hi, [quoted text clipped - 18 lines] Thanks, Jean-Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200508/1 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is this vba code correct or is there a more simple way to do this ?
Sorry it is Nick this time, not Niek :-)
Bob "Bob Phillips" wrote in message ... Seeing Niek's, this too should be Dim oToe As Worksheet Dim nValue As Double Dim i As Long Set oToe = Worksheets("toekomst_oud") With Me nValue = .Range("D12") + .Range("D13") .Range("I27") = oToe.Columns("P:P") + ovalue For i = 1 To 6 .Range("I" & 27 + i) = oToe.Columns(24 + i * 4)) + ovalue Next i End With -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Then it can be simpler Dim oToe As Worksheet Dim nValue As Double Set oToe = Worksheets("toekomst_oud") With Me nValue = .Range("D12") + .Range("D13") .Range("I27") = oToe.Columns("P:P") + ovalue .Range("I28") = oToe.Columns("AB:AB") + ovalue .Range("I29") = oToe.Columns("AF:AF") + ovalue .Range("I30") = oToe.Columns("AJ:AJ") + ovalue .Range("I31") = oToe.Columns("AN:AN") + ovalue .Range("I32") = oToe.Columns("AR:AR") + ovalue .Range("I33") = oToe.Columns("AV:AV") + ovalue End With -- HTH RP (remove nothere from the email address if mailing direct) "Jean-Pierre D via OfficeKB.com" wrote in message ... Hi NickHK, the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening") The code is in the object sheet1(staffelberekening) under workbook 'selection change' NickHK wrote: Jean-Pierre, Which sheets do the ranges Range("D12") + Range("D13") refer to ? NickHk Hi, [quoted text clipped - 18 lines] Thanks, Jean-Pierre -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200508/1 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is this vba code correct or is there a more simple way to do this ?
There're a lot of ways to simplify the code. See if this like you Sub tralla() cpo = Range("D12") + Range("D13") With Sheets("staffelberekening") .Range("I27") = tema("P:P") + cpo .Range("I28") = tema("AB:AB") + cpo .Range("I29") = tema("AF:AF") + cpo .Range("I30") = tema("AJ:AJ") + cpo .Range("I31") = tema("AN:AN") + cpo .Range("I32") = tema("AR:AR") + cpo .Range("I33") = tema("AV:AV") + cpo End With End Sub Function tema(valor As String) As Double tema = Application.Sum(Sheets("toekomst_oud").Columns(val or)) End Functio -- trik ----------------------------------------------------------------------- triki's Profile: http://www.excelforum.com/member.php...fo&userid=2622 View this thread: http://www.excelforum.com/showthread.php?threadid=39525 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I correct an incorrect total from a simple formula? | Excel Discussion (Misc queries) | |||
| Code is not correct | Excel Programming | |||
| Help to correct code | Excel Programming | |||
| Please help correct a simple error | New Users to Excel | |||
| Can you help with the correct translation of this code? | Excel Programming | |||
Hybrid Mode
