Using VBA to Insert a formula in a cell
 

I've extracted a piece of coding that keeps giving me Run-time error '1004':
Application-defined or object defined error. What I'm doing is creating a
blank worksheet, adding titles, and extracting data from other sheets. Most
of the data is pulled directly from the various sheets. In one instance I
need to put a formula in the cell to do some evaluation. Apparently I don't
understand how this is done. The last line below is where I've been
unsuccessful in coding.

'Insert columns into the Discrete Sheet

'Select the Sheet and Insert Titles
Sheets("Resources (Discrete)").Select
Columns("C:I").Select
Selection.Insert Shift:=xlToRight
Range("F2").Select
ActiveCell.FormulaR1C1 = "'SDATE"
Range("G2").Select
ActiveCell.FormulaR1C1 = "'EDATE"

'Set the formatting in the data area
Range("F3:G3").Select
Selection.NumberFormat = "m/d/yyyy"

'Extract the data
Range("F3").Select
ActiveCell.Formula = "=IF('BOE-Data & Text'!R[11]C[1]= ""12:00:00
AM"",='BOE- Data & Text'!RC[-4],='BOE-Data & Text'!R[11]C[1])"

Any help is appreciated. Thanks,

Lee Kirts

Using VBA to Insert a formula in a cell
 

You had one of the sheet names misspelled (extra space) and stray equal
signs. This worked for me (it at least entered the formula - whether the
formula works as you want or not I can't say).


ActiveCell.FormulaR1C1="=IF('BOE-Data & Text'!R[11]" & _
"C[1]= ""12:00:00 AM"",'BOE-Data & Text'!R" & _
"C[-4],'BOE-Data & Text'!R[11]C[1])"



--
Regards,
Tom Ogilvy


"Lee" wrote in message
...
I've extracted a piece of coding that keeps giving me Run-time error
'1004':
Application-defined or object defined error. What I'm doing is creating a
blank worksheet, adding titles, and extracting data from other sheets.
Most
of the data is pulled directly from the various sheets. In one instance I
need to put a formula in the cell to do some evaluation. Apparently I
don't
understand how this is done. The last line below is where I've been
unsuccessful in coding.

'Insert columns into the Discrete Sheet

'Select the Sheet and Insert Titles
Sheets("Resources (Discrete)").Select
Columns("C:I").Select
Selection.Insert Shift:=xlToRight
Range("F2").Select
ActiveCell.FormulaR1C1 = "'SDATE"
Range("G2").Select
ActiveCell.FormulaR1C1 = "'EDATE"

'Set the formatting in the data area
Range("F3:G3").Select
Selection.NumberFormat = "m/d/yyyy"

'Extract the data
Range("F3").Select
ActiveCell.Formula = "=IF('BOE-Data & Text'!R[11]C[1]= ""12:00:00
AM"",='BOE- Data & Text'!RC[-4],='BOE-Data & Text'!R[11]C[1])"

Any help is appreciated. Thanks,

Lee Kirts

Using VBA to Insert a formula in a cell
 

Tom,

Thank you. Think I just stared at too long and needed another set of eyes
to look at it. It threw me when it kept compiling and not giving me any
errors until it ran. Thanks again,

Lee
--------

"Lee" wrote:

I've extracted a piece of coding that keeps giving me Run-time error '1004':
Application-defined or object defined error. What I'm doing is creating a
blank worksheet, adding titles, and extracting data from other sheets. Most
of the data is pulled directly from the various sheets. In one instance I
need to put a formula in the cell to do some evaluation. Apparently I don't
understand how this is done. The last line below is where I've been
unsuccessful in coding.

'Insert columns into the Discrete Sheet

'Select the Sheet and Insert Titles
Sheets("Resources (Discrete)").Select
Columns("C:I").Select
Selection.Insert Shift:=xlToRight
Range("F2").Select
ActiveCell.FormulaR1C1 = "'SDATE"
Range("G2").Select
ActiveCell.FormulaR1C1 = "'EDATE"

'Set the formatting in the data area
Range("F3:G3").Select
Selection.NumberFormat = "m/d/yyyy"

'Extract the data
Range("F3").Select
ActiveCell.Formula = "=IF('BOE-Data & Text'!R[11]C[1]= ""12:00:00
AM"",='BOE- Data & Text'!RC[-4],='BOE-Data & Text'!R[11]C[1])"

Any help is appreciated. Thanks,

Lee Kirts