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

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

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

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

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

#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

Thanks for your input, If I comment out the code to replace the formula with
the value, the spreadsheet looks great, I can see the Username, Store# and
StoreName because the vlookup is working, as soon as I uncomment the code to
replace the formula with the actual value the value in each cell is #NA. (in
my case I will always find a match) In the meantime I recorded a macro to
copy and paste the value by selecting value w/i the dropdown list... If I run
that macro seperately(after I run my macro to create the temp table and
create the vlookup formula) it works, but If I copy the code to my macro I
get the #NA again
below is what I currnently have

I appreciate your patience with working with me on this !!


' Get Store name
MsgBox ("colLocstoreName = " & colLocStoreName) ' tmhx
For Each cell In LookupValueRng
cell.Offset(0, colLocStoreName).Formula = "=Vlookup(" & _
cell.Address & "," & tmpTableRng.Address & ",4,False)"
Next

Call ReplaceFormula

End Sub

Sub ReplaceFormula()
'
' Macro4 Macro
' Macro recorded 8/3/2007 by ToniS
'
'
The below range is the same location as where I have the vlookup Forumla
where it is pulling the data correctly....

Range("F4:F8").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

I am at a complete loss.........





"Tom Ogilvy" wrote:

#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

Do you have Calculation set to manual?

If so, before you replace the values put in

Application.Calculate

although I have never seen this problem.

--
Regards,
Tom Ogilvy


"ToniS" wrote:


Thanks for your input, If I comment out the code to replace the formula with
the value, the spreadsheet looks great, I can see the Username, Store# and
StoreName because the vlookup is working, as soon as I uncomment the code to
replace the formula with the actual value the value in each cell is #NA. (in
my case I will always find a match) In the meantime I recorded a macro to
copy and paste the value by selecting value w/i the dropdown list... If I run
that macro seperately(after I run my macro to create the temp table and
create the vlookup formula) it works, but If I copy the code to my macro I
get the #NA again
below is what I currnently have

I appreciate your patience with working with me on this !!


' Get Store name
MsgBox ("colLocstoreName = " & colLocStoreName) ' tmhx
For Each cell In LookupValueRng
cell.Offset(0, colLocStoreName).Formula = "=Vlookup(" & _
cell.Address & "," & tmpTableRng.Address & ",4,False)"
Next

Call ReplaceFormula

End Sub

Sub ReplaceFormula()
'
' Macro4 Macro
' Macro recorded 8/3/2007 by ToniS
'
'
The below range is the same location as where I have the vlookup Forumla
where it is pulling the data correctly....

Range("F4:F8").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

I am at a complete loss.........





"Tom Ogilvy" wrote:

#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