Solved: VLookup problem when using QueryTables.Add

Hi,

When I load data to a sheet using the following code

Sheet1.Select

Sheet1.Columns("A:Z").Delete

With Sheet1.QueryTables.Add("TEXT;" & "C:\Data.csv", Range("A1"))
.Name = "DoesNotMatter"
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1)
.Refresh BackgroundQuery:=False
End With

I find that all my formulas in say Sheet2 which use vlookup to extract data no longer work and show a REF! error.

ie

=VLOOKUP($E$2,'Weekly Data'!$A2:$J$500,7,FALSE)

becomes

=VLOOKUP($E$2,'Weekly Data'!REF!,7,FALSE)

That causes me to have to re-enter the formulas which is painful.

It does not happen if I paste in the data using copy paste however.

I'm using Office 2007.

Is there a way to prevent this ?

Any help appreciated.

Thanks,

Geoff :think:

Hi Geoff,

Once you delete range A:Z all links to this range will disappear. You better use

Code:

---

Range("A:Z").ClearContents

---

If you really have to delete A:Z then use a named range instead of A1:J500.

Code:

---

Range("A:Z").Delete
    Sheets("Weekly Data").Select
    ActiveWorkbook.Names("ZoekTest").Delete
    Range("A1:J500").Select
    ActiveWorkbook.Names.Add Name:="LookupRange", RefersToR1C1:="=Blad1!R1C1:R500C10"

---

Succes,
Erik

Thanks Erik - worked nicely !!