Perhaps more confusing than I can explain - Loop to remove list item based on list with vlookup association as qualifier results in #NA causing error

arg123

New Member
Joined
Jun 8, 2016
Messages
28
Okay! Here we go! After playing with this for a full two days (maybe long enough, hopefully)... here I am again!

A Review List (E10) is populated on the sheet through a macro. Done.
The drop down list in H10 is populated from that Review List. Awesome.
L10 is a VLOOKUP that uses the selection in the H10 drop down list to populate so whenever you select a dropdown, it updates. Works well enough (duplicates default to the first selection).
The Repeat Selected Review Sample button runs a macro that:
Removes the item from the Review List (copies the sample name to the repeat list) and removes the associated target ID from the the "Sample" column-neighboring "Target" column list.
This does not remove the sample from the drop down, causing a VLOOKUP error #N/A. Which is fine until it causes the loop this is nested in to error out.

Any thoughts on how to just stop the loop if the #N/A is in L10?
I have tried a few things, last of which was a GoTo to try to skip it. No Luck.
Tried ElseIf and Else and a few other things. Nada.

First tried moving an If statement for the L10 qualifier outside the loop, no luck. Which I thought was the easiest thing. But it forgets that the loop doesn't then look at L10. Anyway.

Automatically ending the loop if it bugs out would also be fine.

Any thoughts, greatly appreciated!

Screen cap attached as well as code:

1621105699156.png


VBA Code:
Sub RptRvw()

Range("H10").Copy
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues, Transpose:=False

Dim rvwlist As Range
Set rvwlist = Sheets("Interface").Range("E10:E3094")

For Each cell In rvwlist

Dim cellset As Range
Set cellset = Range(cell, cell.Offset(0, 1))

If Range("L10").Value = "#N/A" Then <-------- Error here "Type mismatch"
    GoTo Skipcell
ElseIf Range("L10").Value <> "#N/A" Then
        If cell.Value = Range("H10").Value And cell.Offset(0, 1).Value = Range("L10").Value Then
        cellset.Delete
        ElseIf cell.Value = Range("H10").Value And cell.Offset(0, 1).Value <> Range("L10").Value Then
        ElseIf cell.Value <> Range("H10").Value And cell.Offset(0, 1).Value = Range("L10").Value Then
        ElseIf cell.Value <> Range("H10").Value And cell.Offset(0, 1).Value <> Range("L10").Value Then
        End If
End If

Skipcell: 
Next

End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Instead of this

VBA Code:
If Range("L10").Value = "#N/A" Then     '<-------- Error here "Type mismatch"

use this

VBA Code:
If Application.WorksheetFunction.IsNA(Range("L10")) Then
 
Upvote 0
Solution
Instead of this

VBA Code:
If Range("L10").Value = "#N/A" Then     '<-------- Error here "Type mismatch"

use this

VBA Code:
If Application.WorksheetFunction.IsNA(Range("L10")) Then

My savior. And Educator. I've become more familiar with the application.worksheetfunction.Is aspect. To think of the times this simple line could have helped in the past. Much appreciated!
 
Upvote 0

Forum statistics

Threads
1,214,987
Messages
6,122,614
Members
449,091
Latest member
gaurav_7829

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top