Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert text from one cell into formula in another cell. | Excel Discussion (Misc queries) | |||
how do I insert a cell value in a hyperlink formula | New Users to Excel | |||
Maintaining a formula in a cell when there was an insert row | Excel Discussion (Misc queries) | |||
Insert value of formula into cell | Excel Programming | |||
insert formula in last cell | Excel Programming |