| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells within Cells formula
Hi Guys
This is an example of one of the formulas that I am trying to use. Dim x As Long x = 2 Cells(x, "C").Formula = "=(Data!Cells(x, "P")+Data!Cells(x,"R")/24" It keeps giving me a compiled error, unexpected end of statement. It keeps pointing to the P and I can't figure out how to get around it. I've tried single quotation marks, putting them in separate brackets and what not but it doesn't work. =/ I'm stumped, guys. Help would be appreciated. -Simon the newbie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
You're mixing VBA code into a formula. You can't do that.Try
something like Cells(x, "C").Formula = "=(Data!P" & x & "+Data!R" & x & ")/24" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mortals" wrote in message oups.com... Hi Guys This is an example of one of the formulas that I am trying to use. Dim x As Long x = 2 Cells(x, "C").Formula = "=(Data!Cells(x, "P")+Data!Cells(x,"R")/24" It keeps giving me a compiled error, unexpected end of statement. It keeps pointing to the P and I can't figure out how to get around it. I've tried single quotation marks, putting them in separate brackets and what not but it doesn't work. =/ I'm stumped, guys. Help would be appreciated. -Simon the newbie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
Try this
I use " twice in the formula Cells(x, "C").Formula = "=(Data!Cells(x, ""P"")+Data!Cells(x,""R"")/24" -- Regards Ron de Bruin http://www.rondebruin.nl "mortals" wrote in message oups.com... Hi Guys This is an example of one of the formulas that I am trying to use. Dim x As Long x = 2 Cells(x, "C").Formula = "=(Data!Cells(x, "P")+Data!Cells(x,"R")/24" It keeps giving me a compiled error, unexpected end of statement. It keeps pointing to the P and I can't figure out how to get around it. I've tried single quotation marks, putting them in separate brackets and what not but it doesn't work. =/ I'm stumped, guys. Help would be appreciated. -Simon the newbie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
Forget my stupid answer
See Chip's response I go to bed <vbg -- Regards Ron de Bruin http://www.rondebruin.nl "mortals" wrote in message oups.com... Hi Guys This is an example of one of the formulas that I am trying to use. Dim x As Long x = 2 Cells(x, "C").Formula = "=(Data!Cells(x, "P")+Data!Cells(x,"R")/24" It keeps giving me a compiled error, unexpected end of statement. It keeps pointing to the P and I can't figure out how to get around it. I've tried single quotation marks, putting them in separate brackets and what not but it doesn't work. =/ I'm stumped, guys. Help would be appreciated. -Simon the newbie |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
or you can use range
Range("C" & x).Formula = "=(Data!P" & x & " + Data!R" & x & ")/24" -- Gary "mortals" wrote in message oups.com... Hi Guys This is an example of one of the formulas that I am trying to use. Dim x As Long x = 2 Cells(x, "C").Formula = "=(Data!Cells(x, "P")+Data!Cells(x,"R")/24" It keeps giving me a compiled error, unexpected end of statement. It keeps pointing to the P and I can't figure out how to get around it. I've tried single quotation marks, putting them in separate brackets and what not but it doesn't work. =/ I'm stumped, guys. Help would be appreciated. -Simon the newbie |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
Thanks guys,
It works fine in a simple formula like what I provided. However, when i tried something more complicated like: Cells(x, "D").Formula = "=WORKDAY(Data!J" & x & ",Data!C" & x & ",Results!D1:D3))" It just gives me a application/object-defined error. =/ |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
You've got an extra closing parentheses at the very end of the
formula. "mortals" wrote in message oups.com... Thanks guys, It works fine in a simple formula like what I provided. However, when i tried something more complicated like: Cells(x, "D").Formula = "=WORKDAY(Data!J" & x & ",Data!C" & x & ",Results!D1:D3))" It just gives me a application/object-defined error. =/ |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
Chip,
You have the eyes of a hawk. I can't believe I missed that double parenthesis. =/ Since you are on a roll and have been of awesome help to me, could you help me figure out why these 2 don't work either? I've checked for double parenthesis, quotations marks and modified it somewhat but nothing seems to work. =/ Cells(x, "G").Formula = "=IF(Data!L" & x & "<"",IF(Data!M" & x & "<"",NETWORKDAYS(Data!L" & x & ",Data!M" & x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""),"")" Thanks in advance |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
In both formulas, you need to double the double quotes (for a
total of four " characters) to insert "" into the formula Cells(x, "G").Formula = "=IF(Data!L" & x & "<"""",IF(Data!M" & _ x & "<"""",NETWORKDAYS(Data!L" & x & ",Data!M" & _ x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""""),"""")" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mortals" wrote in message oups.com... Chip, You have the eyes of a hawk. I can't believe I missed that double parenthesis. =/ Since you are on a roll and have been of awesome help to me, could you help me figure out why these 2 don't work either? I've checked for double parenthesis, quotations marks and modified it somewhat but nothing seems to work. =/ Cells(x, "G").Formula = "=IF(Data!L" & x & "<"",IF(Data!M" & x & "<"",NETWORKDAYS(Data!L" & x & ",Data!M" & x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""),"")" Thanks in advance |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
chip:
is there any performance or any other reason someone would use cells(y,x) rather than range(x,y)? -- Gary "Chip Pearson" wrote in message ... In both formulas, you need to double the double quotes (for a total of four " characters) to insert "" into the formula Cells(x, "G").Formula = "=IF(Data!L" & x & "<"""",IF(Data!M" & _ x & "<"""",NETWORKDAYS(Data!L" & x & ",Data!M" & _ x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""""),"""")" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mortals" wrote in message oups.com... Chip, You have the eyes of a hawk. I can't believe I missed that double parenthesis. =/ Since you are on a roll and have been of awesome help to me, could you help me figure out why these 2 don't work either? I've checked for double parenthesis, quotations marks and modified it somewhat but nothing seems to work. =/ Cells(x, "G").Formula = "=IF(Data!L" & x & "<"",IF(Data!M" & x & "<"",NETWORKDAYS(Data!L" & x & ",Data!M" & x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""),"")" Thanks in advance |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
Its a matter of syntax and style mostly. The Cells property and
the Range property use different syntaxes, and over time one develops a stylistic preference. I don't think there is much of a performance difference between the two, but I've never run any timing tests. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... chip: is there any performance or any other reason someone would use cells(y,x) rather than range(x,y)? -- Gary "Chip Pearson" wrote in message ... In both formulas, you need to double the double quotes (for a total of four " characters) to insert "" into the formula Cells(x, "G").Formula = "=IF(Data!L" & x & "<"""",IF(Data!M" & _ x & "<"""",NETWORKDAYS(Data!L" & x & ",Data!M" & _ x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""""),"""")" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mortals" wrote in message oups.com... Chip, You have the eyes of a hawk. I can't believe I missed that double parenthesis. =/ Since you are on a roll and have been of awesome help to me, could you help me figure out why these 2 don't work either? I've checked for double parenthesis, quotations marks and modified it somewhat but nothing seems to work. =/ Cells(x, "G").Formula = "=IF(Data!L" & x & "<"",IF(Data!M" & x & "<"",NETWORKDAYS(Data!L" & x & ",Data!M" & x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""),"")" Thanks in advance |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cells within Cells formula
ok, thanks. i was just wondering if it was just a preference.
-- Gary "Chip Pearson" wrote in message ... Its a matter of syntax and style mostly. The Cells property and the Range property use different syntaxes, and over time one develops a stylistic preference. I don't think there is much of a performance difference between the two, but I've never run any timing tests. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... chip: is there any performance or any other reason someone would use cells(y,x) rather than range(x,y)? -- Gary "Chip Pearson" wrote in message ... In both formulas, you need to double the double quotes (for a total of four " characters) to insert "" into the formula Cells(x, "G").Formula = "=IF(Data!L" & x & "<"""",IF(Data!M" & _ x & "<"""",NETWORKDAYS(Data!L" & x & ",Data!M" & _ x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""""),"""")" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mortals" wrote in message oups.com... Chip, You have the eyes of a hawk. I can't believe I missed that double parenthesis. =/ Since you are on a roll and have been of awesome help to me, could you help me figure out why these 2 don't work either? I've checked for double parenthesis, quotations marks and modified it somewhat but nothing seems to work. =/ Cells(x, "G").Formula = "=IF(Data!L" & x & "<"",IF(Data!M" & x & "<"",NETWORKDAYS(Data!L" & x & ",Data!M" & x & ",Results!$D$1:$D$3)-1,0),0)" Cells(x, "J").Formula = "=IF(Data!I" & x & "=1,IF(Data!I" & x & "<=6,Data!I" & x & ",""),"")" Thanks in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula refers to empty cells even when cells contains data | Excel Worksheet Functions | |||
| Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
| Need formula that will sum cells in a column bases on criteria in other cells. | Excel Worksheet Functions | |||
| trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
| Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= statement | Excel Programming | |||
Linear Mode
