How to return error.type as a value in Excel Macro 1

[mattiaturalla]

Am trying to delete rows from a spreadsheet where vlookup has left #N/A as the result. There are a couple of good threads as to how to get vlookup to give a different answer - but given that the error.type for #N/A = 7 there really should be some easy way to make this formula work. I have tried lots around this theme;

Range("a1").Select

Do Until ActiveCell.Value = "Stop"

If ActiveCell.Value.Error.Type = 7 Then
Range(ActiveCell, ActiveCell.Offset(0, 7)).Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If

the answers probably obvious - but i just can't see it

 

[PHV]

Have you tried this ?
If ActiveCell.Errors.Item(7).Value = True Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mattiaturalla]

Thanks for the answer PH but have given that a go and still no joy. I have stripped a spreadsheet right down to just a few rows and have used the =error.type(a1) formula to ensure that the cell i'm interrogating is in fact returning error type 7 - aaaaaghhhh

 

[PHV]

And this ?
If Application.WorksheetFunction.ISNA(ActiveCell) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mattiaturalla]

Hooray - that works a treat ... no idea why mind but works a treat thanks. now the bit i thought worked doesn't - could i impose a little further? the 'do until..' line is a problem. i've looked at this problem so long now i wouldn't see the answer if it bit me on the nose.

Range("d4").Select

Do Until ActiveCell.Offset(1, -4).Value = "STOP"

If Application.WorksheetFunction.IsNA(ActiveCell) Then
Range(ActiveCell(0, -4), ActiveCell.Offset(0, 3)).Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If

Loop

 

[PHV]

Replace this:
Do Until ActiveCell.Offset(1, -4).Value = "STOP"
By this:
Do Until ActiveCell.Offset(0, -4).Value = "STOP"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mattiaturalla]

Thanks PH but that hasn't improved matters. Is there more to the Do Until and Loop commands that i have missed?

 

[mattiaturalla]

I've got around this problem by defining the number of times to loop using;

i=75 at the start and;
i = i + 1 before the loop

it isn't exactly what I was after - but essentially I wanted to control how far the macro ran into the spreadsheet - so ... the result is what counts!

Thanks PH for your speedy responses