Macro puts #REF in VLOOKUP formula

G

Guest

I run a simple macro which opens up 6 data files, the VLOOKUP on my receiving
spreadsheet updates, this is then saved and then the data files close. The
data files are overwritten every 30 mins with new data (always in the same
rows). If for whatever reason a data file has been over written with no data,
i.e. is blank, the formula for the VLOOKUP changes from looking for Sheet1 to
#REF which then needs to be changed back. Does anyone know how this happens
and how I can prevent it?

Many thanks
 
V

vezerid

Generally VLOOKUP() produces #REF! when the 3rd argument is larger than
the width of the 2nd argument table, e.g. in

=VLOOKUP(A1, C:E, 4, 0)

Thus you could examine whether the code deletes any columns prior to
refreshing data. No more ideas unless there is something more specific.

Does this help?
Kostis Vezerides
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How Do I Avoid #REF! In Formulas? 4
my vlookup path changes when I open the file 1
VLookup 15
Formulas change when macro adds new column 3
VLOOKUP ? 16
VLOOKUP returns #REF using Query as source 2
How do I copy a vlookup formula without changing the data range? 2
Column reference in Vlookup formula 1

Top