Hi,

I'm trying to put a formula in a cell with my VBA function. The resulting
formula doesn't work, it gives me #NAME? error.

A working formula:

=IF(E7281="<0.005",0.0025,IF(F72810,F7281,E7281))

One put from my VBA function :

=IF(E7292="<0.005",0.0025,IF(F72920,F7292,E7292))

If I simply double-click on the error cell, without editing anything, the
formula works after.

Thanks.

If you assign the string representation of your formula to the Formula
property of the cell and double up the internal quotes so VB sees them as a
quote character instead of a String delimiter, it should work. Here is an
example...

Range("A1").Formula="=IF(E7281=""<0.005"",0.0025,I F(F72810,F7281,E7281))"

Notice that I have used 2 quote marks where there will be 1 quote mark
showing in the formula itself.

--
Rick (MVP - Excel)


"YouWontSpamThisAdress" wrote in
message ...
Hi,

I'm trying to put a formula in a cell with my VBA function. The resulting
formula doesn't work, it gives me #NAME? error.

A working formula:

=IF(E7281="<0.005",0.0025,IF(F72810,F7281,E7281))

One put from my VBA function :

=IF(E7292="<0.005",0.0025,IF(F72920,F7292,E7292))

If I simply double-click on the error cell, without editing anything, the
formula works after.

Thanks.

Hi Rick,

That is what I did... the cut-paste indicated is the result in the actual
cell...

This is my actual code :
formula = "=IF(E" & CurRow & "=""< 0.005"",0.0025,IF(F" & CurRow & "0,F" &
CurRow & " , E" & CurRow & "))"

Thanks

"Rick Rothstein" a écrit dans le
message de news: ...
If you assign the string representation of your formula to the Formula
property of the cell and double up the internal quotes so VB sees them as
a quote character instead of a String delimiter, it should work. Here is
an example...

Range("A1").Formula="=IF(E7281=""<0.005"",0.0025,I F(F72810,F7281,E7281))"

Notice that I have used 2 quote marks where there will be 1 quote mark
showing in the formula itself.

--
Rick (MVP - Excel)


"YouWontSpamThisAdress" wrote in
message ...
Hi,

I'm trying to put a formula in a cell with my VBA function. The resulting
formula doesn't work, it gives me #NAME? error.

A working formula:

=IF(E7281="<0.005",0.0025,IF(F72810,F7281,E7281))

One put from my VBA function :

=IF(E7292="<0.005",0.0025,IF(F72920,F7292,E7292))

If I simply double-click on the error cell, without editing anything, the
formula works after.

Thanks.

I guess I am not entirely sure what problem you are actually having. First,
I'm guessing where you say "This is my actual code", either you meant to
include a cell reference in front of the word "formula", or you meant to
include a dot in front of it (further assuming that there are surrounding
With/EndWith statements for the dot to reference to), or formula is variable
name that will be assigned later on to the Formula property of some cell
reference. The String value you show assigns without error to the Formula
property of a cell reference. If the cell indicated by CurRow of column E
contains this...

< 0.005

(note the space between the less than symbol and the first 0), then the
formula placed by the code displays 0.0025 as it should (the rest of the
formula appears to work correctly as well)... is that internal space causing
your problem by any chance?

--
Rick (MVP - Excel)


"YouWontSpamThisAdress" wrote in
message ...
Hi Rick,

That is what I did... the cut-paste indicated is the result in the actual
cell...

This is my actual code :
formula = "=IF(E" & CurRow & "=""< 0.005"",0.0025,IF(F" & CurRow & "0,F"
& CurRow & " , E" & CurRow & "))"

Thanks

"Rick Rothstein" a écrit dans le
message de news: ...
If you assign the string representation of your formula to the Formula
property of the cell and double up the internal quotes so VB sees them as
a quote character instead of a String delimiter, it should work. Here is
an example...

Range("A1").Formula="=IF(E7281=""<0.005"",0.0025,I F(F72810,F7281,E7281))"

Notice that I have used 2 quote marks where there will be 1 quote mark
showing in the formula itself.

--
Rick (MVP - Excel)


"YouWontSpamThisAdress" wrote
in message ...
Hi,

I'm trying to put a formula in a cell with my VBA function. The
resulting formula doesn't work, it gives me #NAME? error.

A working formula:

=IF(E7281="<0.005",0.0025,IF(F72810,F7281,E7281))

One put from my VBA function :

=IF(E7292="<0.005",0.0025,IF(F72920,F7292,E7292))

If I simply double-click on the error cell, without editing anything,
the formula works after.

Thanks.

Hi Rick,

Here is the actual code :
formula = "=SI(E" & CurRow & "=""<0.005"",0.0025,SI(F" &
CurRow & "0,F" & CurRow & " , E" & CurRow & "))"
With rngMaster
.Offset(j, 0) = certNo
.Offset(j, 1) = Shipment
.Offset(j, 2) = CertDate
.Offset(j, 3) = AssayNo
.Offset(j, 4) = rngData.Offset(i + 5, 2)
.Offset(j, 7).formula = formula
End With

BUT I found the problem... I originally replace the SI in the message with
IF (SI in french is IF in english)

VBA seems to translate the function If to Si when it puts the function to
the cell.

Wonder why VBA was accepting the code though, and why by double-clicking on
the cell corrected the problem.

Thanks.


"Rick Rothstein" a écrit dans le
message de news: ...

I guess I am not entirely sure what problem you are actually having.
First, I'm guessing where you say "This is my actual code", either you
meant to include a cell reference in front of the word "formula", or you
meant to include a dot in front of it (further assuming that there are
surrounding With/EndWith statements for the dot to reference to), or
formula is variable name that will be assigned later on to the Formula
property of some cell reference. The String value you show assigns without
error to the Formula property of a cell reference. If the cell indicated
by CurRow of column E contains this...

< 0.005

(note the space between the less than symbol and the first 0), then the
formula placed by the code displays 0.0025 as it should (the rest of the
formula appears to work correctly as well)... is that internal space
causing your problem by any chance?

--
Rick (MVP - Excel)


"YouWontSpamThisAdress" wrote in
message ...
Hi Rick,

That is what I did... the cut-paste indicated is the result in the actual
cell...

This is my actual code :
formula = "=IF(E" & CurRow & "=""< 0.005"",0.0025,IF(F" & CurRow & "0,F"
& CurRow & " , E" & CurRow & "))"

Thanks

"Rick Rothstein" a écrit dans le
message de news: ...
If you assign the string representation of your formula to the Formula
property of the cell and double up the internal quotes so VB sees them
as a quote character instead of a String delimiter, it should work. Here
is an example...

Range("A1").Formula="=IF(E7281=""<0.005"",0.0025,I F(F72810,F7281,E7281))"

Notice that I have used 2 quote marks where there will be 1 quote mark
showing in the formula itself.

--
Rick (MVP - Excel)


"YouWontSpamThisAdress" wrote
in message ...
Hi,

I'm trying to put a formula in a cell with my VBA function. The
resulting formula doesn't work, it gives me #NAME? error.

A working formula:

=IF(E7281="<0.005",0.0025,IF(F72810,F7281,E7281))

One put from my VBA function :

=IF(E7292="<0.005",0.0025,IF(F72920,F7292,E7292))

If I simply double-click on the error cell, without editing anything,
the formula works after.

Thanks.

VBA is very USA centric.

You'll want to use the American English functions and the American separator
(comma, not semicolon) when you do this kind of thing.

ps. I wouldn't use a variable named formula when VBA has a property called
..Formula. It may not cause confusion for excel, but it would for me.



YouWontSpamThisAdress wrote:

Hi Rick,

Here is the actual code :
formula = "=SI(E" & CurRow & "=""<0.005"",0.0025,SI(F" &
CurRow & "0,F" & CurRow & " , E" & CurRow & "))"
With rngMaster
.Offset(j, 0) = certNo
.Offset(j, 1) = Shipment
.Offset(j, 2) = CertDate
.Offset(j, 3) = AssayNo
.Offset(j, 4) = rngData.Offset(i + 5, 2)
.Offset(j, 7).formula = formula
End With

BUT I found the problem... I originally replace the SI in the message with
IF (SI in french is IF in english)

VBA seems to translate the function If to Si when it puts the function to
the cell.

Wonder why VBA was accepting the code though, and why by double-clicking on
the cell corrected the problem.

Thanks.

"Rick Rothstein" a écrit dans le
message de news: ...

I guess I am not entirely sure what problem you are actually having.
First, I'm guessing where you say "This is my actual code", either you
meant to include a cell reference in front of the word "formula", or you
meant to include a dot in front of it (further assuming that there are
surrounding With/EndWith statements for the dot to reference to), or
formula is variable name that will be assigned later on to the Formula
property of some cell reference. The String value you show assigns without
error to the Formula property of a cell reference. If the cell indicated
by CurRow of column E contains this...

< 0.005

(note the space between the less than symbol and the first 0), then the
formula placed by the code displays 0.0025 as it should (the rest of the
formula appears to work correctly as well)... is that internal space
causing your problem by any chance?

--
Rick (MVP - Excel)


"YouWontSpamThisAdress" wrote in
message ...
Hi Rick,

That is what I did... the cut-paste indicated is the result in the actual
cell...

This is my actual code :
formula = "=IF(E" & CurRow & "=""< 0.005"",0.0025,IF(F" & CurRow & "0,F"
& CurRow & " , E" & CurRow & "))"

Thanks

"Rick Rothstein" a écrit dans le
message de news: ...
If you assign the string representation of your formula to the Formula
property of the cell and double up the internal quotes so VB sees them
as a quote character instead of a String delimiter, it should work. Here
is an example...

Range("A1").Formula="=IF(E7281=""<0.005"",0.0025,I F(F72810,F7281,E7281))"

Notice that I have used 2 quote marks where there will be 1 quote mark
showing in the formula itself.

--
Rick (MVP - Excel)


"YouWontSpamThisAdress" wrote
in message ...
Hi,

I'm trying to put a formula in a cell with my VBA function. The
resulting formula doesn't work, it gives me #NAME? error.

A working formula:

=IF(E7281="<0.005",0.0025,IF(F72810,F7281,E7281))

One put from my VBA function :

=IF(E7292="<0.005",0.0025,IF(F72920,F7292,E7292))

If I simply double-click on the error cell, without editing anything,
the formula works after.

Thanks.






--

Dave Peterson