#N/A means your vlookup formula is not working - rather it is working, but
the values you are looking up are not being found. It has nothing to do with
the part of the code you are asking about.

If I do

=Vlookup("A",B:C,2,False)

and A isn't found in column B, the formula returns #N/A.

Unless you fix your formula or find out why the values are not being
matched, you won't eliminate the #N/A values.

If you accept that there will be some non-matches (in otherwords, the #N.A
are correct) and you just want to get rid of these values

With LongupValuerng.offset(0,colocation)
on Error Resume Next
set rng = .Specialcells(xlFormulas,xlErrors)
on Error goto 0
.Formula = .Value
if not rng is nothing then
rng.Clearcontents
End if
End with

--
Regards,
Tom Ogilvy


"ToniS" wrote:

thanks for the quick response, I did change my code to use the collocation
parm and I still get a value of #NA, I also tried the copy and paste method,
once outside the for next loop and once inside the loop, value returned was
#NA
below is my code I was positive this was going to work......

What would the looping method be? I rather not go that route, but I am
getting
desperate to try and get this working

Again thanks for your help...

LookupValueRng.Offset(0, colLocation).Formula = LookupValueRng.Offset(0,
colLocation).Value

LookupValueRng.Offset(0, colLocName).Copy
LookupValueRng.Offset(0, colLocName).PasteSpecial xlValues

Tonis


"Tom Ogilvy" wrote:

based on your code, your code should be

LookupValueRng.Offset(0, colocation).Formula = LookupValueRng.Offset(0,
colocation).Value

The approach your using is correct. The only other alternative besides
looping would be

LookupValuerng.Offset(0,colocation).copy
LookupValuerng.Offset(0,colocation).PasteSpecial xlValues

--
Regards,
Tom Ogilvy


"ToniS" wrote:

Thanks that fixed my syntx error.

The below code for the most part works... the part that I am trying to
fix is replacing the formula to the value. The cells have vlookup formula
in the cell
instead of the value. As soon as the temp table is deleted, #NA displays in
the cell
because the vlookup failed....

Below is what I have so for, thanks to Tom O. help (THANKS, I really
appreciate it!)

'setup Connection String
strConnection = "ODBC;DSN=NewSportsWeb;UID=;PWD=;Database=NewSport sWeb"

'setup the select statement
strSQL = "SELECT UL.UserID, UL.Name, ML.Store#, ML.StoreName " & _
"FROM UserList UL " & _
"INNER JOIN MemberList ML ON UL.MemberID = ML.MemberID "

With ActiveSheet.QueryTables.Add(Connection:=strConnect ion, _
Destination:=Range("t1"), Sql:=strSQL)
.Refresh
End With

Set tmpTableRng = Range("t2:w1800")
Set LookupValueRng = Range(Range("A4"), Range("A4").End(xlDown))

' returns the correct info in the correct column
For Each cell In LookupValueRng
cell.Offset(0, colLocation).Formula = "=Vlookup(" & _
cell.Address & "," & tmpTableRng.Address & ",2,False)"
Next

LookupValueRng.Offset(0, 1).Formula = LookupValueRng.Offset(0, 1).Value

' tmpTableRng.EntireColumn.Delete





"Tom Ogilvy" wrote:

Just a typo. You left out the second OFFSET
rng.offset(0,1).Formula = rng.Offset(0,1).Value
--
Regards,
Tom Ogilvy


"ToniS" wrote:

I am trying to replace a formula with in a cell and retain the value from the
formula

I have tried the following and get a syntx error

rng.offset(0,1).Formula = rng.(0,1).Value


Any Ideas on what I am doing wrong?

Thanks
ToniS