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

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

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

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

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

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. =/

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. =/

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

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

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

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

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