excel - inserting a formula in a cell (using =) fails

[AndersK]

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

 

[Niek Otten]

Hi Anders,

In VBA, try (for example)

Range("a1").Formula = "=VLOOKUP(B1,C112,2)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Alex Turner]

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

I am sure there is a 'proper' way. But - have you simple tried:

On Error Resume Next
' Error prone code goes here
On Error Goto 0

Otherwise, I have a feeling there is a way of telling Excel not to
automatically update. Is it under 'options'. Anyhow - if you find it,
record a macro and whilst recording, set that option to see how to do it
from vba or vbscript.

AJ

 

[Niek Otten]

And an ending quote of course:

Range("a1").Formula = "=VLOOKUP(B1,C112,2)"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi Anders,
|
| In VBA, try (for example)
|
| Range("a1").Formula = "=VLOOKUP(B1,C112,2)
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||
|| 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
||
||
||
|
|

 

[AndersK]

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