PDA

View Full Version : Excel Lookup Problem



dc88310
04-02-2011, 08:53 PM
Hey...

I am a newbie at VBA code... and I was given some code that theoretically works... but it's not quite what i needed. Mabye someone here may be able to help with it...

What I am doing is taking data from a QA system and preparing it in excel, then uploading it to a database... there is one piece that still illudes me...

When i initially copy and paste the data into the excel sheet... i have code that takes it and moves it to another sheet in the same workbook and formats it the way that I need it. The QA system does not identify their name... only an ID # that correlates with their name... so what i did... was setup a hidden sheet in the workbook that merely loads the most current table from my database so i could have the most updated names, etc...

So... because there are always a different # of entries for the QA data... I am trying to lookup and place the results of the formula in the agent name cell all the way down for each entry... the code i was given puts it in a vba msgbox ...

What I would like to do with this is modify it to where it looked at column A and kept pulling the information over until there was nothing in column A...

Here is the code that i have if someone is willing to help out with their expertise:

Sub IndexMatch1()

For RowNum = 3 To 10

' Get Value to lookup from Source Sheet
LookupValue = Sheets("Grasp Completed").Range("A" & RowNum).Value

' Use Function to find value and return value from same row
Results = ReturnValue("Team Data", LookupValue, 3, 2)

' display restults
MsgBox = ("LookupValue: " & LookupValue & "Results: " & Results)

Next RowNum
End Sub

Public Function ReturnValue(ShName, ValueToFind, LookUpColumnNum, ReturnColumnNum) As String

With Worksheets(ShName)
Set LookUpColumn = .Cells(1, LookUpColumnNum).EntireColumn
On Error Resume Next
RowNum = Application.WorksheetFunction.Match(ValueToFind, LookUpColumn, 0)
If RowNum > 0 Then
ReturnValue = .Cells(RowNum, ReturnColumnNum)
Else
ReturnValue = "N/A"
End If
End With


End Function

Simon Lloyd
04-03-2011, 02:11 AM
A sample workbook would help a great deal!