|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
is this vba code correct or is there a more simple way to do this ?
Bob,
I've been called worse. NickHK "Bob Phillips" wrote in message ... 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 |
is this vba code correct or is there a more simple way to do this ?
The worst thing Nick is that I have called Niek Nick in the past, and I
don't want to make that mistake again. I am confusing myself :-) Bob "NickHK" wrote in message ... Bob, I've been called worse. NickHK "Bob Phillips" wrote in message ... 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 |
| All times are GMT +1. The time now is 05:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com