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