How do I code an Excel formula in Access VBA

[PSULionRP]

I am dynamically creating an Excel spreadsheet in Access via VBA code. One of
my cells created in Excel needs to have an Excel formula:

.Range("C8") = "=If(B2=B3+B4,"Y","N")"

Obviously the Access VBA doesn't like all those double-quotation marks. How
can I make the column "Y" or "N" based on the contents and the Access VBA
code???

Thanks in advance for your hel, and hopeful for a reply.

PSULionRP

 

[Clif McIrvin]

..Range("C8") = "=If(B2=B3+B4,""""Y"""",""""N"""")"

Thats *4* .. count'em, 4 double quotes.

I prefer using chr$(34) for the double quote:

..Range("C8") = "=If(B2=B3+B4," & chr$(34) & "Y" & chr$(34) & "," &
chr$(34) & "N" & chr$(34) & ")"

all on one line. Or, using line continuation:

..Range("C8") = "=If(B2=B3+B4," & chr$(34) _
& "Y" & chr$(34) & "," & chr$(34) & "N" & chr$(34) & ")"

 

[John W. Vinson]

I am dynamically creating an Excel spreadsheet in Access via VBA code. One of
my cells created in Excel needs to have an Excel formula:

.Range("C8") = "=If(B2=B3+B4,"Y","N")"

Obviously the Access VBA doesn't like all those double-quotation marks. How
can I make the column "Y" or "N" based on the contents and the Access VBA
code???

Thanks in advance for your hel, and hopeful for a reply.

PSULionRP

Double the doublequotes within the doublequotes:

..Range("C8") = "=If(B2=B3+B4,""Y"",""N"")"

 

[Clif McIrvin]

Double the doublequotes within the doublequotes:

.Range("C8") = "=If(B2=B3+B4,""Y"",""N"")"

John has more experience than I do ... that's why I like chr$(34) ... I
never can remember how many quotes it takes to do what I want <grin>

 

[James A. Fortune]

I am dynamically creating an Excel spreadsheet in Access via VBA code. One of
my cells created in Excel needs to have an Excel formula:

.Range("C8") = "=If(B2=B3+B4,"Y","N")"

Obviously the Access VBA doesn't like all those double-quotation marks. How
can I make the column "Y" or "N" based on the contents and the Access VBA
code???

Thanks in advance for your hel, and hopeful for a reply.

PSULionRP

How about a mnemonic device to help Clif remember John's advice?

From Mad Max Beyond Thunderdome:

Two go in, one comes out.

Try the following:

With objXL
.Range("C8").Select
.ActiveCell.FormulaR1C1 = "=IF(R[-6]C[-1]=R[-5]C[-1]+R[-4]C
[-1],""Y"",""N"")"
End With

Maybe there's an easier way to refer to the contents of other cells
from a given one that will "stick" for automation from Access. I'll
post back if I discover one.

James A. Fortune
(e-mail address removed)

 

[PSULionRP]

You people are the BEST!

Can I say I LOVE this forum!

Thank You All!

I am dynamically creating an Excel spreadsheet in Access via VBA code. One of
my cells created in Excel needs to have an Excel formula:

.Range("C8") = "=If(B2=B3+B4,"Y","N")"

Obviously the Access VBA doesn't like all those double-quotation marks. How
can I make the column "Y" or "N" based on the contents and the Access VBA
code???

Thanks in advance for your hel, and hopeful for a reply.

PSULionRP

How about a mnemonic device to help Clif remember John's advice?

From Mad Max Beyond Thunderdome:

Two go in, one comes out.

Try the following:

With objXL
.Range("C8").Select
.ActiveCell.FormulaR1C1 = "=IF(R[-6]C[-1]=R[-5]C[-1]+R[-4]C
[-1],""Y"",""N"")"
End With

Maybe there's an easier way to refer to the contents of other cells
from a given one that will "stick" for automation from Access. I'll
post back if I discover one.

James A. Fortune
(e-mail address removed)
.

 

[Clif McIrvin]

You people are the BEST!

Can I say I LOVE this forum!

Thank You All!

Yes, I like this forum too -- I learn a lot here!

You're welcome.

 

[PSULionRP]

Jez....I sure hope you guys are monitoring this thread....

Ok.....that seemed to work. But when my Excel spreadsheet opens from Access,
it actually displays the formula itself within the Excel spreadsheet.

=IF(B2=B3+B4,"Y","N")

If I copy and paste it to another cell, it works! So why is it displaying
the formula in the cell I created it in and NOT actually executing it???

Please Help....

Thanks!

I am dynamically creating an Excel spreadsheet in Access via VBA code. One of
my cells created in Excel needs to have an Excel formula:

.Range("C8") = "=If(B2=B3+B4,"Y","N")"

Obviously the Access VBA doesn't like all those double-quotation marks. How
can I make the column "Y" or "N" based on the contents and the Access VBA
code???

Thanks in advance for your hel, and hopeful for a reply.

PSULionRP

How about a mnemonic device to help Clif remember John's advice?

From Mad Max Beyond Thunderdome:

Two go in, one comes out.

Try the following:

With objXL
.Range("C8").Select
.ActiveCell.FormulaR1C1 = "=IF(R[-6]C[-1]=R[-5]C[-1]+R[-4]C
[-1],""Y"",""N"")"
End With

Maybe there's an easier way to refer to the contents of other cells
from a given one that will "stick" for automation from Access. I'll
post back if I discover one.

James A. Fortune
(e-mail address removed)
.

 

[Clif McIrvin]

By chance has that cell gotten formatted as Text? If so, that would
cause the formula to be ignored.

Maybe something like:

With objXL
With .Range("C8")
.NumberFormat = "General"
.Value = "=If(B2=B3+B4,""Y"",""N"")"
End With
End With

--
Clif

Jez....I sure hope you guys are monitoring this thread....

Ok.....that seemed to work. But when my Excel spreadsheet opens from
Access,
it actually displays the formula itself within the Excel spreadsheet.

=IF(B2=B3+B4,"Y","N")

If I copy and paste it to another cell, it works! So why is it
displaying
the formula in the cell I created it in and NOT actually executing
it???

Please Help....

Thanks!

I am dynamically creating an Excel spreadsheet in Access via VBA
code. One of
my cells created in Excel needs to have an Excel formula:

.Range("C8") = "=If(B2=B3+B4,"Y","N")"

Obviously the Access VBA doesn't like all those double-quotation
marks. How
can I make the column "Y" or "N" based on the contents and the
Access VBA
code???

Thanks in advance for your hel, and hopeful for a reply.

PSULionRP

How about a mnemonic device to help Clif remember John's advice?

From Mad Max Beyond Thunderdome:

Two go in, one comes out.

Try the following:

With objXL
.Range("C8").Select
.ActiveCell.FormulaR1C1 = "=IF(R[-6]C[-1]=R[-5]C[-1]+R[-4]C
[-1],""Y"",""N"")"
End With

Maybe there's an easier way to refer to the contents of other cells
from a given one that will "stick" for automation from Access. I'll
post back if I discover one.

James A. Fortune
(e-mail address removed)
.

 

[James A. Fortune]

.ActiveCell.FormulaR1C1 = "=IF(R[-6]C[-1]=R[-5]C[-1]+R[-4]C
[-1],""Y"",""N"")"
End With

Maybe there's an easier way to refer to the contents of other cells
from a given one that will "stick" for automation from Access. I'll
post back if I discover one.

Use the Formula property instead of the FormulaR1C1 property for
"normal" cell referencing.

ActiveCell.Formula = "=IF(B2 = B3 + B4,""Y"",""N"")"

If you don't want B2 = B3 + B4 to change relatively when copying and
pasting, try:

ActiveCell.Formula = "=IF($B$2 = $B$3 + $B$4,""Y"",""N"")"

James A. Fortune
(e-mail address removed)