PDA

View Full Version : Solved: VLookup problem when using QueryTables.Add



gmulhall
04-29-2007, 08:48 PM
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:

WinteE
04-30-2007, 10:30 AM
Hi Geoff,

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


Range("A:Z").ClearContents

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


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

gmulhall
04-30-2007, 06:25 PM
Thanks Erik - worked nicely !!