VBA Help

S

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
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
'--------------------------------------------------------------------------------
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
If sheet title is NOT this Monday's date then execute code
Replies
3
Views
583
MARK858
MARK858
L
  • Question Question
Importing Outlook Data
Replies
0
Views
678
Legacy 512328
L
O
  • Question Question
User-defined type not defined - vba/excel error
Replies
4
Views
290
RoryA
RoryA
M
  • Question Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
755
MountainFog
M
S
  • Question Question
VBA lookup by taking common column from user
Replies
2
Views
198
StephenCrump
S

Forum statistics

Threads
1,224,802
Messages
6,181,054
Members
453,014
Latest member
Chris258

Share this page

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