#N/A - 2 options

[Turin]

Greetings all, I have a N/A problem.

I have a set of data produced from a vlookup and it returns everythin
exactly as I wish.

I have created a macro that does all the preceding work for me in tha
it creates this list, however I cannot find a way in VBA to delet
these N/A entries.

1.
ISNA works if I input it manually into the cells, but not if i use VB
to input the formula - WHY???? how irrating as it lets me use VBA t
do the normal vlookup!!!!

Range("E7").Formula
"=IF(isNA(vlookup(A7,VMS,2,false)),"",vlookup(A7,VMS,2,false))"

2.
All the methods I have tried (looping, find etc) have failed when
have tasked them to delete the N/A cells

I cant copy all the values of the range either I need the Vlookup t
remain established - ARRGHHHHH!!!!!!!

Does anyone have any bright ideas as to how it is possible to remov
N/A's or stop them occuring in the first place with VBA???

Help greatly appreciated

Greg :eek

 

[Jim Cone]

Turin,

It seems to work if you replace ,"", with ,"""",
(two more sets of double quote marks)

Jim Cone
San Francisco, USA


"Turin"
<[email protected]>
wrote in message

Greetings all, I have a N/A problem.
I have a set of data produced from a vlookup and it returns everything
exactly as I wish.
I have created a macro that does all the preceding work for me in that
it creates this list, however I cannot find a way in VBA to delete
these N/A entries.
1.
ISNA works if I input it manually into the cells, but not if i use VBA
to input the formula - WHY???? how irrating as it lets me use VBA to
do the normal vlookup!!!!

Range("E7").Formula =
"=IF(isNA(vlookup(A7,VMS,2,false)),"",vlookup(A7,VMS,2,false))"

2.
All the methods I have tried (looping, find etc) have failed when I
have tasked them to delete the N/A cells
I cant copy all the values of the range either I need the Vlookup to
remain established - ARRGHHHHH!!!!!!!
Does anyone have any bright ideas as to how it is possible to remove
N/A's or stop them occuring in the first place with VBA???
Help greatly appreciated
Greg --
Turin