simple macro

W

widman

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?
 
W

ward376

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

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

Cliff Edwards
 
W

widman

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.
 
W

ward376

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
 
W

widman

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.
 
W

ward376

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
 
W

ward376

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top