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