获取应用程序定义或对象定义的错误 [英] Getting Application-defined or object-defined error

问题描述

我是 Excel/VBA 和 StackOverflow 的新手.

我运行的是 Excel 2007 版

我在这个问题上花了一整天时间，已经耗尽了我的耐心.尝试执行简单的 VLOOKUP 时，我不断收到应用程序定义或对象定义错误".我正在测试此示例代码以解决更大的问题:

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:

我需要一个位于 Sheet1 中的命令按钮，它将具有 VBA 代码以根据 Sheet1 中的单元格值执行表查找.该表在 Sheet2 中定义.查找(我假设是 VLOOKUP)需要查找名称并发回包含例如Salary"的数据.然后将此工资信息插入到 Sheet1 上的另一个单元格中.然后，用户可以更新此工资数字.另一个命令按钮会将其导出回表格(更新表格条目).

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

这可能吗?但是，我无法通过 VLOOKUP 传递简单的代码来显示消息框(见下文).

Is this possible? However I cannot get passed simple code to display a message box via a VLOOKUP (see below).

这是我的 Sheet1:A1:4

Here is my Sheet1: A1:4

Dave
John
Sara
Steve

这是我的 Sheet2，定义为 Table1 (A2:B6)

Here is my Sheet2 which is defined as Table1 (A2:B6)

Name    Salary
Dave    2500
John    3500
Sara    4000
Steve   4500

这是我的 VBA 代码:(注意对尝试"的注释)

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

推荐答案

试试这个代码.

FINDSAL 从 sheet1 A1 中获取名称(您可以轻松更改它)，在 sheet2 范围 Table1 中查找工资，如果找到了工资- 写在 sheet1 A2.

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

下一步，updateSalary从sheet1A1中读取name，从sheet1A2中读取salary，并尝试更新Table1中的salary值 在 sheet2 上

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.

这篇关于获取应用程序定义或对象定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！