CODEWITHSUNDEEP
excelvba
Geminiflipflop
Geminiflipflop

Reputation: 119

Using a VLookup in an IF Statement

As a newbie i'm really stuck with this and would love any help anyone can offer.

I am trying to use an VLookup in an IF statement but as teh VLookup returns #N/A for a negative result, the If statement does not recognize this and errors out instead of proceeding.

Public Const ParametersAssembly = "TabDocumentPath|strFrameworkPath|FrameworkFullPath|FrameworkAllFile|AssembliesPath|FrameworkTabs|SaveAsExtension|CopyTabsBefore"
Public Const ElementSeparator = "|"

vList = Split(ParameterList, ElementSeparator, -1, vbTextCompare)

For Each vParameter In vList
    If Application.WorksheetFunction.VLookup(vParameter, Worksheets("Configuration").Range("A:E"), 1, False) <> vParameter Then        
       MsgBox vParameter " does not exist in range"       
    End If
Next vParameter

I am currently just searching the first column in a range A:E. This might suffice. Would it be possible to check every column in the range A:E without having a separate VLookup?

Again, any help with this is very much appreciated.

Thank you!!

Upvotes: 1

Views: 112

Answers (2)

SJR
SJR

Reputation: 23081

So here is one approach, using Find. You could loop through the individual columns if required, but without knowing more about what you are trying to achieve not sure what would be useful.

Sub x()

Dim r As Range, vList

Public Const ParametersAssembly = "TabDocumentPath|strFrameworkPath|FrameworkFullPath|FrameworkAllFile|AssembliesPath|FrameworkTabs|SaveAsExtension|CopyTabsBefore"
Public Const ElementSeparator = "|"

vList = Split(ParameterList, ElementSeparator, -1, vbTextCompare)

For Each vParameter In vList
    Set r = Worksheets("Configuration").Range("A:E").Find(What:=vParameter, LookAt:=xlWhole, _
                          MatchCase:=False, SearchFormat:=False)
    If r Is Nothing Then
       MsgBox vParameter & " does not exist in range"
    End If
Next vParameter

End Sub

Upvotes: 1

user4039065
user4039065

Reputation:

Never use worksheet function if you are planning to evaluate the outcome. Use Application.Vlookup returned to a variant and test with IsError before checking the value.

This is testing for existence only so Application.Match would be more efficient and you only have to test if there is no match.

' ...
For Each vParameter In vList
    If IsError(Application.Match(vParameter, Worksheets("Configuration").Range("A:A"), 0)) Then        
       MsgBox vParameter " does not exist in range"       
    End If
Next vParameter
' ...

Upvotes: 4

Related Questions