• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting error 1004 in the code.

Jagdev Singh

Active Member
Hi Experts

I am trying to find the sum of dynamic range in column C with the below code. But I am getting runtime error 1004 – Application Defined or Object-defined error msg.

Range("C65536").End(xlUp).Offset(1, 0) = "=Sum($C:" & Range("C65536").End(xlUp).Address & ")"

Please let me know what I am doing wrong here.

Regards,

JD
 
JD

You just missed the row ref ...

Range("C65536").End(xlUp).Offset(1, 0) = "=Sum($C1:" & Range("C65536").End(xlUp).Address & ")"

Another Approach...

Range("C65536").End(xlUp)(2) = "=Sum($C$1:" & Range("C65536").End(xlUp).Address & ")"
 
Hi ,

When you use the column format for a reference , both the start and end point references have to be column references. For example , you can have the following :

=SUM($J$7 : $J$33)

where both the start and end points are static , or you can have

=SUM($H : $H)

where both the start and end points are column references.

You cannot have a mixed reference such as :

=SUM($D : $D100)

where the start is a column reference , while the end point is a static reference.

Narayan
 
Here's some sample code below. Replace ".Address" with ".Select" to select the range instead.

Code:
Sub RngFind()

Dim RngA, RngB, RngC As String

'Find last non-blank cell in column
RngA = Range("C" & Cells(Rows.Count, "C").End(xlUp).Row).Address

'Find the blank cell below the last non-blank cell in column
RngB = Range("C" & Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row).Address

'Find entire non-blank range in column
RngC = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Address


End Sub
 
Hi All

Thanks for giving you valuable input on the above thread.

@Deepak - Thanks for pointing out the error of not adding a digit 1 in the code. It worked like a charm!

@Narayan – Thanks Narayan for explaining the concept of static formula to be used in the VBA. Will try to implement this logic in near future

@ Chiraya – Really appreciate you concepts for finding the dynamic range for a column.


All the tips are fantastic and informative. Thanks for explaining each concept in detail and proper understanding.

Regards.
JD
 
Back
Top