Error 1004, Method 'Range' of object '_Worksheet' failed

Question

I have lots of data (numbers) with heading in several worksheets that I am trying to zero. This is done on each column as follows: Taking the value of the first row in the column and subtracting this value from all rows in the column.

I have put together this code (may not be the best way, but Im new to VBA so :))

Dim ws As Worksheet
Dim Header As Range, Coldata As Range
Dim firstrow As Long
Dim cell As Range, cell2 As Range

Set ws = ActiveSheet
Set Header = ws.Range("B5:CJ5")

For Each cell In Header
    If cell Is Nothing Then Exit For
    firstrow = cell.Offset(2).Value
    ***Set Coldata = ws.Range(cell.offset(3),cell.Offset(3)).End(xlDown)***
    cell.Value = 0
    For Each cell2 In Coldata
        cell2.Value = cell2.Value - firstrow
    Next
Next
MsgBox "Done zeroing"

This sub is under the Module of the workbook I am working on. Whenever I run this sub from inside the VBA window it gives me the error I stated in the description on the Line of the code with **** around it.

When I try to run it from a workhsheet it says Cannot run the macro. The macro may not be avaiable in this worksheet or all macros may be disabled. The thing is I run another macro in the same module it works, so macros being disabled is out of the question.

What am I missing?

Thanks in advance!

Edit, I fixed it.. But running it takes SO much time? Excel freezes when I run it though?

Gary's Student Gary's Student · Accepted Answer · 2016-05-02T01:27:48

You are over-specifying. Replace:

Set Coldata = ws.Range(cell.Offset(3)).End(xlDown)

with:

Set Coldata = cell.Offset(3).End(xlDown)

cell is fully qualified already.

Error 1004, Method 'Range' of object '_Worksheet' failed

1 Answers