I have used the method of inserting a formula in a cell using '=

if one insert "=a1" in a cell (a2) the excel treats this string as a
formula and executes its.

But i a application I insert a vlookup in cells

In a2 is placed a name and in a2 is then filles with
"=vlookup(anothersheets!a:b"...)
If the formula is inserted directly in the cell excel executes it.

However when the vba program tries to insert the "=vlookup..."
statement in a2 it stops with an error because the vlookup fails
itself .
The application was suppoed to find the names which was missing but I
exected it to insert "=vlookup..." in cells a2,b2,.. and thereafter
expected to see N#A in cell a2,b2 or these cells where the name in a1
was missing.

What should I do so vba is not stopping the updating.
I have tried the om local error... which works in the program, but the
vlookup is not inserted in the a2..

Another approach is of course to let the program examine if there is a
name in the second sheet but why as the excel has the excellent
formula?
And the names in sheet two is only filled out after the the names in
sheet one is inserted.

Does anyone have a clue (maybe stop calculating while inserting - how
should one do that)?

thnkx
Anders Karbek

Hi Anders,

In VBA, try (for example)

Range("a1").Formula = "=VLOOKUP(B1,C1:D12,2)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"AndersK" wrote in message ...
|
| I have used the method of inserting a formula in a cell using '=
|
| if one insert "=a1" in a cell (a2) the excel treats this string as a
| formula and executes its.
|
| But i a application I insert a vlookup in cells
|
| In a2 is placed a name and in a2 is then filles with
| "=vlookup(anothersheets!a:b"...)
| If the formula is inserted directly in the cell excel executes it.
|
| However when the vba program tries to insert the "=vlookup..."
| statement in a2 it stops with an error because the vlookup fails
| itself .
| The application was suppoed to find the names which was missing but I
| exected it to insert "=vlookup..." in cells a2,b2,.. and thereafter
| expected to see N#A in cell a2,b2 or these cells where the name in a1
| was missing.
|
| What should I do so vba is not stopping the updating.
| I have tried the om local error... which works in the program, but the
| vlookup is not inserted in the a2..
|
| Another approach is of course to let the program examine if there is a
| name in the second sheet but why as the excel has the excellent
| formula?
| And the names in sheet two is only filled out after the the names in
| sheet one is inserted.
|
| Does anyone have a clue (maybe stop calculating while inserting - how
| should one do that)?
|
| thnkx
| Anders Karbek
|
|
|

And an ending quote of course:

Range("a1").Formula = "=VLOOKUP(B1,C1:D12,2)"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Niek Otten" wrote in message ...
| Hi Anders,
|
| In VBA, try (for example)
|
| Range("a1").Formula = "=VLOOKUP(B1,C1:D12,2)
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "AndersK" wrote in message ...
||
|| I have used the method of inserting a formula in a cell using '=
||
|| if one insert "=a1" in a cell (a2) the excel treats this string as a
|| formula and executes its.
||
|| But i a application I insert a vlookup in cells
||
|| In a2 is placed a name and in a2 is then filles with
|| "=vlookup(anothersheets!a:b"...)
|| If the formula is inserted directly in the cell excel executes it.
||
|| However when the vba program tries to insert the "=vlookup..."
|| statement in a2 it stops with an error because the vlookup fails
|| itself .
|| The application was suppoed to find the names which was missing but I
|| exected it to insert "=vlookup..." in cells a2,b2,.. and thereafter
|| expected to see N#A in cell a2,b2 or these cells where the name in a1
|| was missing.
||
|| What should I do so vba is not stopping the updating.
|| I have tried the om local error... which works in the program, but the
|| vlookup is not inserted in the a2..
||
|| Another approach is of course to let the program examine if there is a
|| name in the second sheet but why as the excel has the excellent
|| formula?
|| And the names in sheet two is only filled out after the the names in
|| sheet one is inserted.
||
|| Does anyone have a clue (maybe stop calculating while inserting - how
|| should one do that)?
||
|| thnkx
|| Anders Karbek
||
||
||
|
|

On Thu, 20 Dec 2007 15:02:22 -0600, Alex Turner


Thnkx for the reply, but it showed up that it was a mistake from my
side

In my country the standard are using ; between paramters (else it
fails (as an example sum(a1;a2) where it is sum(a1,a2) in USA and in
formulas) when inputting it in a cell by using formula "," is to be
used

This I didnt know.
Now it works
Thnkx again for your time
Anders karbek