VLookup using VBA

[dvermette]

I am attempting to write a script in VBA for an excel spreadsheet. What I want it to do is lookup a company name in one column and enter information in a different column but a the same row the company is found in.

I tried using the autofilter and offset but it dumps the information in a hidden row and column, not the filtered row. Please help!!!

What I have written sofar is below, I know it may not be the pretty but for a beginner VBA'r, not bad. It would work if I could only get over this one hurdle. Suggestions. Thanks

Dim nCredit As String
Dim nCounterparty As String
Dim rNumber As Integer


Static skipCode As Integer

    Sheets("Administration").Activate
    nCredit = Cells(6, 5).Value
        If nCredit = "" Then
        A = MsgBox("Please enter credit analyst name in drop down box.", vbOKOnly)
        GoTo Line4
        End If
        
    Sheets("Credit Request Form").Activate
    nCounterparty = Cells(14, 1).Value
        If nCounterparty = "" Then
        A = MsgBox("No counterparty name in cell A14, please try again.", vbOKOnly)
        GoTo Line4
        End If
          
    
'Enter Credit Analyst name in Credit Request Worksheet
    Application.ScreenUpdating = False
    Workbooks.Open ("O:\SEM\Common\Credit Contract Requests\Credit Request Status Report.xls")
    Workbooks("Credit Request Status Report.xls").Activate
    Sheets("Summary").Activate
    Selection.AutoFilter Field:=3, Criteria1:=nCounterparty
     
    ActiveCell.Offset(2, 7).Activate
    ActiveCell.Value = nCredit
    Selection.AutoFilter
    Workbooks("Credit Request Status Report").Save
    ActiveWorkbook.Close
    ActiveWorkbook.Close
    ActiveWorkbook.Save


Line4:

End Function

EDIT: added code tags - Moderator

 

[Smitty]

Welcome to the Board!

Take a look at the Find Method in the VBA helpfile. It's got a good example as well.

You could streamline your process if you replace the message box method with an InputBox. Just use the answer as the Find criteria.

Hope that helps,

Smitty