Macro Runtime errors

[mhsmalls]

I am running EXCEL 10. I am trying to create a macro that sums up numbers from B55, B8;D8,B1111, etc.. The macro works fine until I get to cellsB47:47 then when I try to run the macro I get "Runtime error '1004':application defined or object defined error. I have no idea what I am doing wrong and when I step into the macro I can't make heads or tales of the syntext. Please help!

 

[Claus Busch]

Hi,

Am Wed, 19 Mar 2014 04:10:27 -0700 (PDT) schrieb (e-mail address removed):

I am running EXCEL 10. I am trying to create a macro that sums up numbers from B55, B8;D8,B1111, etc.. The macro works fine until I get to cells B47:47 then when I try to run the macro I get "Runtime error '1004':application defined or object defined error. I have no idea what I am doing wrong and when I step into the macro I can't make heads or tales of the syntext. Please help!

can you show us your code?

Regards
Claus B.

 

[mhsmalls]

I am running EXCEL 10. I am trying to create a macro that sums up numbersfrom B55, B8;D8,B1111, etc.. The macro works fine until I get to cellsB47:47 then when I try to run the macro I get "Runtime error '1004':application defined or object defined error. I have no idea what I am doing wrong and when I step into the macro I can't make heads or tales of the syntext. Please help!

=SUM(B55,B88,B1111,B1414,B1717,B2020,B2323,B2626,B2929,B3232,B3535,B3838,B4141,B4444,B4747,B5050,B5353,B5656,B5959,B6262,B6565,B6868,B7171,B7474,B7777,)

 

[Claus Busch]

Hi,

Am Wed, 19 Mar 2014 11:20:38 -0700 (PDT) schrieb (e-mail address removed):

=SUM(B55,B88,B1111,B1414,B1717,B2020,B2323,B2626,B2929,B3232,B3535,B3838,B4141,B4444,B4747,B5050,B5353,B5656,B5959,B6262,B6565,B6868,B7171,B7474,B7777,)

if you use a function in VBA you have to write it like:
=Worksheetfunction.Sum(....
To make the code more readable try:
Sub Sum()
Dim myStart As Long
Dim myStop As Long
Dim i As Long
Dim mySum As Double

myStart = 5
myStop = 77

For i = myStart To myStop Step 3
mySum = mySum + WorksheetFunction.Sum(Range(Cells(i, 2), Cells(i,
4)))
Next
Range("B1") = mySum
End Sub

Or:

Sub Sum_2()
Dim myStart As Long
Dim myStop As Long
Dim i As Long
Dim myRng As Range

myStart = 5
myStop = 77

For i = myStart To myStop Step 3
If myRng Is Nothing Then
Set myRng = Range(Cells(i, 2), Cells(i, 4))
Else
Set myRng = Union(myRng, Range(Cells(i, 2), Cells(i, 4)))
End If
Next
Range("C1") = WorksheetFunction.Sum(myRng)
End Sub


Regards
Claus B.

 

[mhsmalls]

I am running EXCEL 10. I am trying to create a macro that sums up numbersfrom B55, B8;D8,B1111, etc.. The macro works fine until I get to cellsB47:47 then when I try to run the macro I get "Runtime error '1004':application defined or object defined error. I have no idea what I am doing wrong and when I step into the macro I can't make heads or tales of the syntext. Please help!

Thanks for the help but is there any way I can do bu using the "Record Macro" function under the "developer" tab. I'm confused as to how I'm supposedto write the instructions in the VBA module. Also, I can't figure out whymy other macro's work and this one doesn't.

 

[Claus Busch]

Hi,

Am Thu, 20 Mar 2014 03:50:19 -0700 (PDT) schrieb (e-mail address removed):

Thanks for the help but is there any way I can do bu using the "Record Macro" function under the "developer" tab. I'm confused as to how I'm supposed to write the instructions in the VBA module. Also, I can't figure out why my other macro's work and this one doesn't.

your macro fails because of false syntax.
Try:
Range("A1") = WorksheetFunction.Sum(Range("B55,B88,B1111,B1414,"
_
& "B1717,B2020,B2323,B2626,B2929,B3232,B3535,B3838,"
_
& "B4141,B4444,B4747,B5050,B5353,B5656,B5959,B6262,"
_
& "B6565,B6868,B7171,B7474,B7777"))

Or:
Range("B1").Formula = "=SUM(B55,B88,B1111,B1414,B1717," _
& "B2020,B2323,B2626,B2929,B3232,B3535,B3838,B4141,"
_
& "B4444,B4747,B5050,B5353,B5656,B5959,B6262,B6565,"
_
& "B6868,B7171,B7474,B7777)"


Regards
Claus B.

 

[mhsmalls]

I am running EXCEL 10. I am trying to create a macro that sums up numbersfrom B55, B8;D8,B1111, etc.. The macro works fine until I get to cellsB47:47 then when I try to run the macro I get "Runtime error '1004':application defined or object defined error. I have no idea what I am doing wrong and when I step into the macro I can't make heads or tales of the syntext. Please help!

I went into the Developer's tab, clicked "Record Macro" and began to type in the Syntax you gave me. It probably didn't work because I didn't type itin correctly. Am I supposed to type in, and I quote(without using quote symbol) Range("B1").Formula="SUM(B55,B88,B1111,etc........? I can domanually do use =Sum(B55, etc... and it works well, I just wanted to create a Macro to save time. Do I include the " and & and _ when I'm writing the syntax? I tried it and it didn't work. If you think I'm stupid, it's O.K. I'm just trying to understand

 

[Claus Busch]

Hi,

Am Fri, 21 Mar 2014 05:24:42 -0700 (PDT) schrieb (e-mail address removed):

I went into the Developer's tab, clicked "Record Macro" and began to type in the Syntax you gave me. It probably didn't work because I didn't type it in correctly. Am I supposed to type in, and I quote(without using quote symbol) Range("B1").Formula="SUM(B55,B88,B1111,etc........? I can do manually do use =Sum(B55, etc... and it works well, I just wanted to create a Macro to save time. Do I include the " and & and _ when I'm writing the syntax? I tried it and it didn't work. If you think I'm stupid, it's O.K. I'm just trying to understand

if you want to record a macro start recorder and make everthing you want
to record by hand. If your work is done stop the recorder.
If you want do work with my macros press Alt+F11 => Insert Module and
paste the code into the code window of the module. Then back in the
sheet run the macro.

Regards
Claus B.

 

[Claus Busch]

Hi,

Am Fri, 21 Mar 2014 05:24:42 -0700 (PDT) schrieb (e-mail address removed):

I went into the Developer's tab, clicked "Record Macro" and began to type in the Syntax you gave me. It probably didn't work because I didn't type it in correctly. Am I supposed to type in, and I quote(without using quote symbol) Range("B1").Formula="SUM(B55,B88,B1111,etc........? I can do manually do use =Sum(B55, etc... and it works well, I just wanted to create a Macro to save time. Do I include the " and & and _ when I'm writing the syntax? I tried it and it didn't work. If you think I'm stupid, it's O.K. I'm just trying to understand

look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for workbook "Sum2"
Right click and download the file because macros are disabled in
OneDrive. Open the workbook => Press Alt+F11 to see where the code
should be placed.


Regards
Claus B.