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
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