Returning a Vlookup function in VBA Excel 2003

[newclasshero]

Hi all:

It has been some time since I have posted. I just can't seem to stay within my comfort zone with excel.

I have 2 sheets named "Input" and "ActivityOffset". The sheets are set up as follows:

Input Sheet
Column A: Activity/Offset Codes. This column is validated by the named range "AO" as below.
Column B and C: Date and Amount

ActivityOffsetMasterList Sheet
Column A: Activity/Offset codes that have become the dynamic named range "AO" that validates Column A of the Input sheet.
Column B: Working Interest (as a percent)

The idea is that as I put my data into the Input sheet, the Activity Offset codes are validated against the Activity Offset Codes contained in the MasterList sheet. I then add more information to the row to log in the Activity/Offset Statement such as the date and amount.

My goal is to have a dialogue box that pops up and that shows me other data (Working interest percent) that corresponds to the ActivityOffset code I just entered into Column A of my Input sheet before I finish filling out the rest of the row with the statement details. I tried to modify and combine some code and a function to do the trick and while I was able to get part of the solution using each idividually, when I put the pieces together the I could not make it work.

The code I am working with is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng1 As Range

Set rng = Range("A1:B10")
If Intersect(Target, rng) Is Nothing Then Exit Sub
MsgBox "Hi, you have selected cell " & Target.Address & vbCrLf & "Please input a number", vbInformation

End Sub

I have been trying to modify this so it returns the the Working Interest instead of the msg: "Hi, you have selected..." I figured the Vlookup function could be inserted into the code:

=VLOOKUP(**cell reference**,AOTable,2,FALSE)

and consequently return the Working Interest amount in the dialogue box.

The code I got to this point is:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng As Range
Dim wi As Integer
Dim InputString As String

Set Rng = Range("A:A")
Set InputString = Intersect(Target, Rng)
Set wi = Application.WorksheetFunction.VLookup(InputString, AOTable, 3, False)


If Intersect(Target, Rng) Is Nothing Then Exit Sub
MsgBox "Working Interest: " & wi & vbCrLf & "Please input a number", vbInformation

End Sub

The code does not return an error, in fact it does nothing at all. Not being a programmer I happy to get that far. I do not however expect that my code is even close to an answer.

I am sorry if my explanation is convoluted (and long-winded) but I would be happy to clarify anything at all. I would appreciate any assistance or insight.

Thanks a lot.

nch

 

[sunnyland]

Hello,

Instead of using vlookup in a macro I prefer to use the function find which I found easier to handle if no match is found. So here a solution slighty modified. It will display a message if the code is not in the database. The find will find the first occurence of the match result, so I suppose this will work perfectly if your codes in activity offset are unique.
MsgBox "Working Interest: " & result.Offset(0, 1)
In my understanding the interest is in the next column beside the code in the activityoffset sheet.

The code will go on the input sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim result As Variant
Set Rng = Range("A:A")
If Not Intersect(Target, Rng) Is Nothing Then
Set result = Worksheets("activityoffset").Range("aotable").Find(Target.Value)
If Not result Is Nothing Then
MsgBox "Working Interest: " & result.Offset(0, 1)
Else
MsgBox "Entry not found in database"
End If
End If
End Sub


Hope that this code does the trick

 

[newclasshero]

Thank you very much Sunnyland. I will give it a try at work tomorrow and let you know how it goes.

nch