Convert Macro to custom function

C

Chad

Hi

I would like to convert this macro to a custom function. Basically
what I would like to do is recreate a vlookup in a custom function. I
want to say If the cell the vlookup points to is either A, B, C or D
then do one thing, else do another.

Here is the code which works in a normal macro. I would like a
function where I could go

=checkit(A1,True Value here, False Value here)

Thanks in advance.

Chad



Sub Checkit()

Dim RngCell As Range
Dim MyList() As Variant
Dim res As Variant
Dim Last As Long
Dim X As Range
Dim ws As Worksheet


Last = Range("B" & Rows.Count).End(xlUp).Row
MyList() = Array("A", "B", "C", "D")

Set X = Range("B2:B" & Last)
For Each RngCell In X
res = Application.Match(RngCell.Value, MyList, 0)
If IsError(res) Then
'One thing here
Else
'Another here
End If
Next RngCell

End Sub
 
J

joel

Function Checkit(target as Range, MatchResults as String,
NonMatchResults as String)

Select Case Target
Case "A","B","C","D"
Checkit = MatchResults
case Else
Checkit = NonMatchResults
end select

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top