is this vba code correct or is there a more simple way to do this ?

  • Thread starter Thread starter Jean-Pierre D via OfficeKB.com
  • Start date Start date
J

Jean-Pierre D via OfficeKB.com

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
 
Hi NickHK,
the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening")
The code is in the object sheet1(staffelberekening) under workbook 'selection
change'


Jean-Pierre,
Which sheets do the ranges Range("D12") + Range("D13") refer to ?

NickHk
Hi,
Click to expand...
[quoted text clipped - 18 lines]
Thanks,
Jean-Pierre
Click to expand...
Click to expand...
 
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(valor))
End Functio
 
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 said:
Hi NickHK,
the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening")
The code is in the object sheet1(staffelberekening) under workbook 'selection
change'


Jean-Pierre,
Which sheets do the ranges Range("D12") + Range("D13") refer to ?

NickHk
Hi,
Click to expand...
[quoted text clipped - 18 lines]
Thanks,
Jean-Pierre
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Hi NickHK,
the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening")
The code is in the object sheet1(staffelberekening) under workbook 'selection
change'


Jean-Pierre,
Which sheets do the ranges Range("D12") + Range("D13") refer to ?

NickHk
Hi,
Click to expand...
[quoted text clipped - 18 lines]
Thanks,
Jean-Pierre
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Hi NickHK,
the ranges Range("D12") + Range("D13") refer to sheets("staffelberekening")
The code is in the object sheet1(staffelberekening) under workbook 'selection
change'


Jean-Pierre,
Which sheets do the ranges Range("D12") + Range("D13") refer to ?

NickHk

Hi,

[quoted text clipped - 18 lines]
Thanks,
Jean-Pierre
Click to expand...
Click to expand...
Click to expand...
 
Sorry it is Nick this time, not Niek :-)

Bob


Bob Phillips said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Bob,
I've been called worse.

NickHK

Bob Phillips said:
Sorry it is Nick this time, not Niek :-)

Bob


Bob Phillips said:
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 said:
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)


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Bob,
I've been called worse.

NickHK

Bob Phillips said:
Sorry it is Nick this time, not Niek :-)

Bob


Bob Phillips said:
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)


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)


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top