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))

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))

--

Dave Peterson

This example uses column H

Sub looker_upper()
Dim v As Variant
Set rr = Intersect(ActiveSheet.UsedRange.SpecialCells(xlCel lTypeFormulas),
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

--
Gary''s Student - gsnu200781


"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))

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.

"Dave Peterson" wrote:

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))

--

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

Aposto wrote:

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.

"Dave Peterson" wrote:

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))

--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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

Aposto wrote:

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.

"Dave Peterson" wrote:

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))

--

Dave Peterson


--

Dave Peterson