VBA Help

[sutclpa]

Hi All,


I currently have a formula, which i would like to perform via vba instead.

the formula is as follows:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,"~","~~"),"?","~?"),"*","~*"),
'OPID Report'!$A1:$B25000,2,FALSE)

I have been trying to do this for a while now, but with no joy.

The way i had considered doing this was, using if statements which would look something like this:

If Application.WorksheetFunction.Left(rngRequested) = "*" Then
rngRequested = Application.WorksheetFunction.Substitute(rngRequested, "*", "~*") 

End If

If Application.WorksheetFunction.Left(rngRequested) = "?" Then
rngRequested = Application.WorksheetFunction.Substitute(rngRequested, "?", "~?")
End If

If Application.WorksheetFunction.Left(rngRequested) = "~" Then
rngRequested = Application.WorksheetFunction.Substitute(rngRequested, "~", "~~")
End If

employee = Application.WorksheetFunction.VLookup(rngRequested, OpidReport, 2, False) 'Name
If Err <> 0 Then
   ActiveCell.Offset(0, 1).Value = "User Not Found" 

Else

Operator = employee
    ActiveCell.Offset(0, 1).Value = Operator

Obviously the above doesn't work, but hopefully it will give you the principle of what i want to do. Basically, if the first substitute criteria is met, it should perform the substitute function and then go to the vlookup, if it isn't met it should move onto the second one. It should loop through each criteria on this basis, and if none of the substitute criteria are met then it should just perform the vlookup.

Is anybody able to help me out with this?

Thanks,

 

[BrianB]

Correct VBA for Left() is

If  Left("1234", 1) = "1" Then

Once we start using VBA we get infinitely more control of things.
Not sure how you want to use the lookup value, but this is the basic code :-

'===============================================================================
'- VBA ALTERNATIVE TO VLOOKUP
'- LOOK UP ACTIVE CELL VALUE IN COLUMN A OF ANOTHER SHEET, RETURN COLUMN B VALUE
'- Select cell containing value to look up & run the macro.
'- Brian Baulsom July 2007 using Excel 2000
'===============================================================================
Sub MY_VLOOKUP()
    Dim rngRequested As String
    Dim ReturnValue As String
    Dim FoundCell As Range
    '---------------------------------------------------------------------------
    '- set find value (add "~" to begining of selected value)
    rngRequested = "~" & ActiveCell.Value
    '---------------------------------------------------------------------------
    '- Find
    With Worksheets("OPID Report")
        Set FoundCell = _
            .Range("A:A").Find(What:=rngRequested, _
            After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        '------------------------------------------------------------------------
        '- Return Value
        If FoundCell Is Nothing Then
            ReturnValue = "n/a"
        Else
            ReturnValue = .Cells(FoundCell.Row, 2).Value
        End If
        '------------------------------------------------------------------------
    End With
    '----------------------------------------------------------------------------
    MsgBox (ReturnValue)
End Sub
'--------------------------------------------------------------------------------

 

[sutclpa]

Thank you so much for your reply. Your code seems so much better than just performing a vlookup in excel. And i can't believe a just diidn't consider

rngRequested = "~" & ActiveCell.Value

as a way to get around the wildcard problem.

Thanks again, really appreciated your help.