Run Time 1004 on pasting formula into cell.

[floydian]

I am getting a runtime 1004 error (Application-defined or object-defined error) with the following code:
*Note - If I cut and paste the formula directly into F2 it works just fine.

Thank you ahead of time!!!

Please Login or Register  to view this content.

[Solus Rankin]

Does it change anything if you put .value after FormulaR1C1?

[flyboy54]

Instead of Activecell.formulaR1C1 = ...try
Activecell.Value = ...

[HaHoBe]

Hi, floydian,

don´t use FormulaR1C1 when using A1 notation - AFAIK it should read

Please Login or Register  to view this content.

Ciao,
Holger

[floydian]

Interesting: I did a "Record Macro" and it changed the forumla and it now works. I don't understand how it does... but it does.
The new wormula:

Please Login or Register  to view this content.

Based on the posts above: Is there a "Better" way to do what I did or is the fix I posted just as efficient?

[HaHoBe]

Hi, floydian,

because the macro recorder used R1C1 notation.

Ciao,
Holger

[floydian]

Which might as well be in French.
I'm trying to learn.... but it's coming slowly.

[HaHoBe]

Hi, floydian,

let´s take E5 (in VBA I would use Range("E5") for that). If you go into the options in Excel you will find R1C1 notation which would switch the column headers to display the number of the columns as opposed to the letters right now. R5C5 would be the equivalent to E5 (Column 5, Row 5, in VBA Cells(5, 5) or Cells(5, "E") as the row number always needs a numeric value while the column may either be letter(s) or a numeric value).

HTH,
Holger

[Solus Rankin]

Perhaps this should be another thread, but I'm assuming this is the difference between absolute and relative.

What points do you use to determine which one you want to use?

I started in .Net not VBA where these distinctions are less necessary.

[HaHoBe]

Hi, XeRo Solus,

Perhaps this should be another thread

Agreed.

It depends on what I want to do. If filling formulas into cells I would rely on the R1C1 notation which would deliver an absolute cell reference if you use R5C5 meaning E5 and a relative one using R[5]C[5] standing for activecell and offset of 5 rows down and 5 column to the right (negative values would be up and to the left).

I mostly work with Range which may be set like

Please Login or Register  to view this content.

except if I had to loop through columns and rows with non-adjacent cells (if avoiding to build up ranges first or using Arrays with Row numbers and Column letters).

Any use referring to the original cell may be covered by using Offset like

Please Login or Register  to view this content.

Please Login or Register  to view this content.

where i is a Long (at least if I were to Dim it). And of course that may be negative values but for A1 it´s hard to imagine that there is a negative column or row (should lead to a run-time error).

Please check the VBA help for more information on that: go to the VBE, press F2 to get up the Object Catalogue and enter either Cells or Range into the search field and have a read about these items and where they are located in the Excel Application model. Or check out http://support.microsoft.com/kb/291308.

HTH,
Holger