Getting Application-defined or object-defined error
I'm new to both Excel/VBA and StackOverflow.
I'm running Excel version 2007
I have spent all day on this problem and I have exhausted my patience. I keep getting "Application-defined or object-defined error" when attempting to perform a simple VLOOKUP. I'm testing this sample code to get to my bigger issue:
I need a Command Button located in Sheet1 that will have VBA code to perform a table lookup based on a cell value in Sheet1. The table is defined in Sheet2. The lookup (VLOOKUP I assume) will need to lookup the name and send back data that contains say "Salary" for example. This salary information is then inserted in another cell on Sheet1. The user can then update this Salary figure. Another Command Button will export it back to the table (Update the table entry).
Is this possible? However I cannot get passed simple code to display a message box via a VLOOKUP (see below).
Here is my Sheet1: A1:4
Dave John Sara Steve
Here is my Sheet2 which is defined as Table1 (A2:B6)
Name Salary Dave 2500 John 3500 Sara 4000 Steve 4500
Here is my VBA code: (note the comments on severy "tries")
Sub FINDSAL() Dim E_name As String Dim Res As Variant 'On Error Resume Next 'Err.Clear 'ThisWorkbook.Sheets("Sheet2").Activate 'ActiveSheet.Range("A1:B5").Select E_name = "John" 'Res = Application.WorksheetFunction.VLookup(E_name, Sheet1.Range("A2:B5"), 3, False) Res = Application.WorksheetFunction.VLookup(E_name, Table1, 2, False) MsgBox "Salary is: $" & Res End Sub
Try this code.
FINDSAL gets name from sheet1 A1 (you can easily change it), finds the salary in sheet2 range Table1 and if salary is found - writes it in sheet1 A2.
Sub FINDSAL() Dim E_name As String Dim Res As Variant Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = ThisWorkbook.Worksheets("Sheet1") Set sh2 = ThisWorkbook.Worksheets("Sheet2") E_name = sh1.Range("A1") Res = Application.VLookup(E_name, sh2.Range("Table1"), 2, False) If Not IsError(Res) Then MsgBox "Salary is: $" & Res sh1.Range("A2") = Res Else MsgBox "Nothing found" End If End Sub
Next step, updateSalary reads name from sheet1 A1 and salary from sheet1 A2 and tries to update salary value in Table1 on sheet2
Sub updateSalary() Dim E_name As String Dim newSalary As Variant Dim rnd As Range Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = ThisWorkbook.Worksheets("Sheet1") Set sh2 = ThisWorkbook.Worksheets("Sheet2") With sh1 E_name = .Range("A1") newSalary = .Range("A2") End With Set Rng = sh2.Range("Table1").Find(What:=E_name, LookAt:=xlWhole) If Not Rng Is Nothing Then Rng.Offset(, 1) = newSalary MsgBox "Salaty updated" Else MsgBox "Can't find " & E_name & " in table" End If End Sub
This code is assumes that all names are unique.