VBA Help

sutclpa

Board Regular
Joined
Dec 21, 2006
Messages
83
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:


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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Correct VBA for Left() is
Code:
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 :-
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

Board Regular
Joined
Dec 21, 2006
Messages
83
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
Code:
rngRequested = "~" & ActiveCell.Value

as a way to get around the wildcard problem. :oops:

Thanks again, really appreciated your help.
 

Forum statistics

Threads
1,181,055
Messages
5,927,863
Members
436,573
Latest member
CMR237

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
Top