I use the vlookup on dozens of sheets each day, always returning #n/a
somewhere in the column. So I copy, paste value, then replace #n/a with
nothing.

When I tried to make a macro do do it, instead of the highlighted range on
the new sheet it wanted to find the original sheet and ranges.

What do I need to look at whatever is highligted on the active sheet, copy,
paste value, and replace #n/a with nothing?

You could use isna in your formulas to keep the na values from
appearing:

=IF(ISNA(VLOOKUP(B1,$D$1:$E$7,2,FALSE)),"",(VLOOKU P(B1,$D$1:$E
$7,2,FALSE)))

Cliff Edwards

way too much to type 50 times a day, especially in sheets that are already at
or past the limit of my 1GB of ram.

"ward376" wrote:

You could use isna in your formulas to keep the na values from
appearing:

=IF(ISNA(VLOOKUP(B1,$D$1:$E$7,2,FALSE)),"",(VLOOKU P(B1,$D$1:$E
$7,2,FALSE)))

Cliff Edwards

You only have to enter them once... then you can copy or fill the
rest. Are you typing/using the function wizard to enter the vlookups?

Cliff Edwards

I just tried recordin the macro again and this time it worked.

I end up typing it in several different columns, in several different
worksheets every day, looking for information on different sheets, typically
dragging it down anywhere from 300 lines to 6500 lines, depending on the
sheet I'm working with.

"ward376" wrote:

You only have to enter them once... then you can copy or fill the
rest. Are you typing/using the function wizard to enter the vlookups?

Cliff Edwards

Sub Macro1()
With ActiveSheet.UsedRange
.Value = .Value
.Replace What:="#N/A", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=True, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub

I can't recommend using this but you could also do this:

Sub Macro1()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.Text = "#N/A" Then c.Formula = ""
Next
End Sub

Cliff Edwards

Change "Activesheet.usedrange" to "Selection" in the examples.

Cliff Edwards