Range Offset Error 1004

[excelaspire0219]

Hi, I am using the following macro to format a sheet - I recently added a auto-fill part to it which essentially moves a number from one column to another and autofills it in all the cells until it encounters another number of the same kind in the column it moved the previous number from. When I try to run this macro it does everything upto the auto-fill part and then gives me error 1004 [the set cell = cell.offset(1,0) ] is highlighted in yellow.
I cannot figure out what could be wrong, I would appreciate your help with this!!
Thanks.

Please Login or Register  to view this content.

[Leith Ross]

Hello excelaspire0219,

The title of your post is important. The more descriptive your title is about your problem, the more likely you are to receive a fast response. It will help you get your question answered quickly and correctly as well as help other members and guests to find answers to the same or similar problem.

A better title would be "Range Offset Error 1004".

If you need helping in changing the title of your post let me know. Click here to send me a message.

[mudraker]

Whilst looping through the cells if an exact match of END OF REPORT is not found then you will continue to loop to the last row and get an error message when trying to set a range beyond the last row



To test add this code immediatle before your Set Cell = Cell.Offset(1, 0) command

Please Login or Register  to view this content.

I notice you are also selecting columns before deleting them.
In VBA Selecting is rarely required

As an example this code

Please Login or Register  to view this content.

Can be replaced with

Please Login or Register  to view this content.

[excelaspire0219]

Thanks Mudraker. It turns out that I was adding a column before doing the the move, therefore although the number I was looking for was in column B it moved to column D just before that segment of the code ran. Your solution helped me detect this - thanks again

[mudraker]

Glad to hear I helped you solve your problem.