Macro to look for where vLookup has a result then paste value

[Aposto]

I'm looking for a macro to look for cells in a column where a vlookup formula
has returned a value other than blank, and then paste the result in that same
cell thus removing the formula. If the value is blank then I want to keep the
formula.
=IF(V6="","-",VLOOKUP(V6,'[AMS Crew list.xls]Crew Info table'!$A:$E,2,FALSE))

 

[Dave Peterson]

Dim myRng as range
dim myCell as range

with worksheets("SomeSheetNameHere")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with

for each mycell in myrng.cells
if mycell.value = "" _
or mycell.value = "-" then
'do nothing
else
mycell.value = mycell.value
end if
next mycell

If figured you'd want to keep the hyphens and the blanks.

I wonder what you want to happen if 0 is returned because the sending cell is
empty?

I'm looking for a macro to look for cells in a column where a vlookup formula
has returned a value other than blank, and then paste the result in that same
cell thus removing the formula. If the value is blank then I want to keep the
formula.
=IF(V6="","-",VLOOKUP(V6,'[AMS Crew list.xls]Crew Info table'!$A:$E,2,FALSE))

 

[Gary''s Student]

This example uses column H

Sub looker_upper()
Dim v As Variant
Set rr = Intersect(ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas),
Range("H:H"))
s = "VLOOKUP"
For Each r In rr
f = r.Formula
v = r.Value
If InStr(f, s) <> 0 Then
If v <> "" Then
r.Value = r.Value
End If
End If
Next
End Sub

 

[Aposto]

Both macro work but I get a "Type mismatch" error and I belive it is because
the formula is returning an error #N/A. How would you tell the macro to leave
the formula in if an error is the value.

Dim myRng as range
dim myCell as range

with worksheets("SomeSheetNameHere")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with

for each mycell in myrng.cells
if mycell.value = "" _
or mycell.value = "-" then
'do nothing
else
mycell.value = mycell.value
end if
next mycell

If figured you'd want to keep the hyphens and the blanks.

I wonder what you want to happen if 0 is returned because the sending cell is
empty?

I'm looking for a macro to look for cells in a column where a vlookup formula
has returned a value other than blank, and then paste the result in that same
cell thus removing the formula. If the value is blank then I want to keep the
formula.
=IF(V6="","-",VLOOKUP(V6,'[AMS Crew list.xls]Crew Info table'!$A:$E,2,FALSE))

 

[Dave Peterson]

Dim myRng as range
dim myCell as range

with worksheets("SomeSheetNameHere")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with

for each mycell in myrng.cells
if iserror(mycell.value) then
'do nothing
else
if mycell.value = "" _
or mycell.value = "-" then
'do nothing
else
mycell.value = mycell.value
end if
end if
next mycell

Both macro work but I get a "Type mismatch" error and I belive it is because
the formula is returning an error #N/A. How would you tell the macro to leave
the formula in if an error is the value.

Dim myRng as range
dim myCell as range

with worksheets("SomeSheetNameHere")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with

for each mycell in myrng.cells
if mycell.value = "" _
or mycell.value = "-" then
'do nothing
else
mycell.value = mycell.value
end if
next mycell

If figured you'd want to keep the hyphens and the blanks.

I wonder what you want to happen if 0 is returned because the sending cell is
empty?

I'm looking for a macro to look for cells in a column where a vlookup formula
has returned a value other than blank, and then paste the result in that same
cell thus removing the formula. If the value is blank then I want to keep the
formula.
=IF(V6="","-",VLOOKUP(V6,'[AMS Crew list.xls]Crew Info table'!$A:$E,2,FALSE))

 

[Aposto]

Bingo works like a charm. Thanks to both you a Gary for your assistance!

Dim myRng as range
dim myCell as range

with worksheets("SomeSheetNameHere")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with

for each mycell in myrng.cells
if iserror(mycell.value) then
'do nothing
else
if mycell.value = "" _
or mycell.value = "-" then
'do nothing
else
mycell.value = mycell.value
end if
end if
next mycell

Both macro work but I get a "Type mismatch" error and I belive it is because
the formula is returning an error #N/A. How would you tell the macro to leave
the formula in if an error is the value.

Dim myRng as range
dim myCell as range

with worksheets("SomeSheetNameHere")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with

for each mycell in myrng.cells
if mycell.value = "" _
or mycell.value = "-" then
'do nothing
else
mycell.value = mycell.value
end if
next mycell

If figured you'd want to keep the hyphens and the blanks.

I wonder what you want to happen if 0 is returned because the sending cell is
empty?



Aposto wrote:

I'm looking for a macro to look for cells in a column where a vlookup formula
has returned a value other than blank, and then paste the result in that same
cell thus removing the formula. If the value is blank then I want to keep the
formula.
=IF(V6="","-",VLOOKUP(V6,'[AMS Crew list.xls]Crew Info table'!$A:$E,2,FALSE))