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) & ")"

--
Clif

"PSULionRP" <PSUL...@discussions.microsoft.com> wrote in message
news:00A2BB78-4FAB-403B...@microsoft.com...

[John W. Vinson]

Double the doublequotes within the doublequotes:

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

--

John W. Vinson [MVP]

[Clif McIrvin]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:n00bh55qne790ovur...@4ax.com...

> 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>

--
Clif

[James A. Fortune]

On Dec 1, 3:23 pm, PSULionRP <PSULio...@discussions.microsoft.com>
wrote:

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
MPAP...@FortuneJames.com

[PSULionRP]

You people are the BEST!

Can I say I LOVE this forum!

Thank You All!

"James A. Fortune" wrote:

> .
>

[Clif McIrvin]

"PSULionRP" <PSUL...@discussions.microsoft.com> wrote in message

news:E6F821DF-6364-4D5E...@microsoft.com...

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

--
Clif

[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!

"James A. Fortune" wrote:

> .
>

[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

"PSULionRP" <PSUL...@discussions.microsoft.com> wrote in message

news:04CD6FDD-56CE-4E5B...@microsoft.com...

--
Clif

[James A. Fortune]

On Dec 1, 5:04 pm, "James A. Fortune" <MPAPos...@FortuneJames.com>
wrote:

> .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
MPAP...@FortuneJames.com