Run-time error 1004 with column counter

**GriffinCarpenter** · 09-30-2014, 07:48 AM

Hello Excel Forum,

On one worksheet I have a range of column headers. Underneath this range of column headers I'd like to copy and paste values that are calculated in another sheet. In the first step I set a variable equal to the length of the column headings, but I'm getting the run-time error 1004: method range of object worksheet failed. I'm thinking this may be an easy syntax problem but I couldn't find any solutions in the forum or through google.

This is my command button code:

Please Login or Register  to view this content.

My workbook is quite large and still in development so I have copied and pasted the three relevant worksheets to a new document:Modified Workbook for Excelforum.xlsm Let me know if any more information is required.

Many thanks in advance!
Griffin

**Olly** · 09-30-2014, 07:58 AM

This line doesn't make sense:

Please Login or Register  to view this content.

Do you mean:

Please Login or Register  to view this content.

**romperstomper** · 09-30-2014, 07:59 AM

Nice simple fix:

Please Login or Register  to view this content.

needs to be:

Please Login or Register  to view this content.

**GriffinCarpenter** · 09-30-2014, 09:12 AM

Thanks both, works a charm.

Just for learning, elsewhere in this workbook I use range instead of cells in this statement:

Please Login or Register  to view this content.

Could you tell me why "range" works there?

Cheers,
Griffin

**Olly** · 09-30-2014, 09:18 AM

You're using the correct argument for the Range object, in that case, with a cell reference as a single argument (albeit in this case, that single argument is constructed by concatenating a constant and a variable)

In your original code, you referred to .Range(6, columns count) - you are referring to a cell by referencing the row and column number as separate arguments, so need to use the Cells object. In this case, the Range objects sees 6 and columns.count as separate arguments, and expects each argument to be a range reference. Neither of those values are range references, so it returns an error.