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