Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF formula syntax problem
Hi folks,
I'm trying to get my code to insert an IF formula into a cell... Range("D2").Select ActiveCell.Formula = "=IF(C2='Y',1,0)" but I keep getting an object-defined error. I've tried variations of quotes and parens. but the best I am able to come up with is having it populate the formula as a string. What am I missing? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF formula syntax problem
Hi Stephen,
Try ActiveCell.Formula = "=IF(C2=""Y"",1,0)" Regards, Stefi €˛Stephen€¯ ezt Ć*rta: Hi folks, I'm trying to get my code to insert an IF formula into a cell... Range("D2").Select ActiveCell.Formula = "=IF(C2='Y',1,0)" but I keep getting an object-defined error. I've tried variations of quotes and parens. but the best I am able to come up with is having it populate the formula as a string. What am I missing? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF formula syntax problem
Well what do you know! I guess that's about the only combination I didn't
try. Thank you very much! "Stefi" wrote: Hi Stephen, Try ActiveCell.Formula = "=IF(C2=""Y"",1,0)" Regards, Stefi €˛Stephen€¯ ezt Ć*rta: Hi folks, I'm trying to get my code to insert an IF formula into a cell... Range("D2").Select ActiveCell.Formula = "=IF(C2='Y',1,0)" but I keep getting an object-defined error. I've tried variations of quotes and parens. but the best I am able to come up with is having it populate the formula as a string. What am I missing? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF formula syntax problem
I think you may need to refer to cell C2 in R1C1 reference style, i.e.:
Range("D2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Y"",1,0)" "Stephen" wrote in message ... Hi folks, I'm trying to get my code to insert an IF formula into a cell... Range("D2").Select ActiveCell.Formula = "=IF(C2='Y',1,0)" but I keep getting an object-defined error. I've tried variations of quotes and parens. but the best I am able to come up with is having it populate the formula as a string. What am I missing? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF formula syntax problem
Alternatively, a long winded way but may help you:
Sub test2() Dim strEval As String strEval = Range("C2").Value If strEval = "Y" Then Range("D2").Value = 1 ElseIf srteval = 0 Then Range("D2").Value = 0 End If End Sub "Stephen" wrote in message ... Hi folks, I'm trying to get my code to insert an IF formula into a cell... Range("D2").Select ActiveCell.Formula = "=IF(C2='Y',1,0)" but I keep getting an object-defined error. I've tried variations of quotes and parens. but the best I am able to come up with is having it populate the formula as a string. What am I missing? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF formula syntax problem
You are welcome! Thanks for the feedback!
Stefi €˛Stephen€¯ ezt Ć*rta: Well what do you know! I guess that's about the only combination I didn't try. Thank you very much! "Stefi" wrote: Hi Stephen, Try ActiveCell.Formula = "=IF(C2=""Y"",1,0)" Regards, Stefi €˛Stephen€¯ ezt Ć*rta: Hi folks, I'm trying to get my code to insert an IF formula into a cell... Range("D2").Select ActiveCell.Formula = "=IF(C2='Y',1,0)" but I keep getting an object-defined error. I've tried variations of quotes and parens. but the best I am able to come up with is having it populate the formula as a string. What am I missing? Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF formula syntax problem
Graham,
thanks for jumping in but Stefi's solution worked like a charm. Now my problem is that I can't seem to get the formula to FillDown. Here's what I got so far... Sub Formula_fill() Dim lLastRow As Long Range("D2").Select ActiveCell.Formula = "=IF(C2=""Y"",1,0)" lLastRow = Range("D65536").End(xlUp).Row If lLastRow 2 Then Range("D2:D" & lLastRow).FillDown Else End If End Sub "Graham Whitehead" wrote: I think you may need to refer to cell C2 in R1C1 reference style, i.e.: Range("D2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Y"",1,0)" "Stephen" wrote in message ... Hi folks, I'm trying to get my code to insert an IF formula into a cell... Range("D2").Select ActiveCell.Formula = "=IF(C2='Y',1,0)" but I keep getting an object-defined error. I've tried variations of quotes and parens. but the best I am able to come up with is having it populate the formula as a string. What am I missing? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF formula syntax problem
ok but I'm not sure how that is going to help me loop through to the end of
the datat in column "C". Let me give you some backround... A user opens the ..xls and clicks a button which prompts them to input a starting and an ending date range. Those parameters are passed to an SQL query which is run against a remote SQL DB and retuns the data from the record set to sheet1 to columns A,B, and C. Before returning the data the code clears the previous contents of the columns then populates row 1 with the field names, and retuens the rest of the data in the recordset to the columns. Each time this runs the number of row returned will be different. Now I want column "D" to use this formula ActiveCell.Formula = "=IF(C2=""Y"",1,0)" to evaluate if the value in column "C" is "Y" or "N" and return either a 0 or 1 to the row in column "D" (this is now popluating for D2). I need this evaluation to run for all the data in the "C" rows. Then I plan to run a SUM againt column "D" and use that number to divide into the total number of rows returned from the dataset, to give the user a percentage. Any suggestions? "Graham Whitehead" wrote: Alternatively, a long winded way but may help you: Sub test2() Dim strEval As String strEval = Range("C2").Value If strEval = "Y" Then Range("D2").Value = 1 ElseIf srteval = 0 Then Range("D2").Value = 0 End If End Sub "Stephen" wrote in message ... Hi folks, I'm trying to get my code to insert an IF formula into a cell... Range("D2").Select ActiveCell.Formula = "=IF(C2='Y',1,0)" but I keep getting an object-defined error. I've tried variations of quotes and parens. but the best I am able to come up with is having it populate the formula as a string. What am I missing? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax problem | Excel Programming | |||
Syntax problem | Excel Worksheet Functions | |||
Syntax Problem with formula code | Excel Programming | |||
Another Syntax Problem | Excel Programming |