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
EDIT: added code tags - Moderator
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
Code:
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