Getting Application-defined or object-defined error

Question

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

Add Option Explicit to the top of your code. You will find your error immediately. — enderland

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-01-30T22:08:12

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.

Getting Application-defined or object-defined error

1 Answers