VBA错误1004 - 范围类的选择方法失败 [英] VBA error 1004 - select method of range class failed

问题描述

第一次海报，所以如果有任何格式化或指导方针我没有遵守，请让我知道，以便我可以解决它。所以我基本上是向用户询问excel文件的文件目录，然后设置一些变量（原来设置为public，因为这些变量被用作项目变量并在其他地方改变）。我还添加了将这些变量设置为空的行（以防万一，我不认为它应该是重要的）。然后我将这些变量设置为我要访问的excel文件，工作簿和工作表。

  Dim filepath as String 
 filePath = CStr（fileDialog）'请求文件目录，设置为字符串
 Dim sourceXL As Variant'这三个是源项目变量
 Dim sourceBook As Variant 
 Dim sourceSheet As Variant 
 Dim sourceSheetSum As Variant 
 
将sourceXL = Nothing'设置为没有什么事情？ 
设置sourceBook = Nothing 
设置sourceSheet = Nothing 
设置sourceSheetSum = Nothing 
 
设置sourceXL = Excel.Application'设置为所需的路径
设置sourceBook = sourceXL.Workbooks.Open（filePath）
设置sourceSheet = sourceBook.Sheets（Measurements）
设置sourceSheetSum = sourceBook.Sheets（Analysis Summary）
 
 Dim measName作为Variant'创建变量以访问
 Dim partName作为变量
 
 sourceSheetSum.Range（C3）。选择'这是问题线
 
 measName = sourceSheetSum.Range（Selection，Selection.End（xlDown））。value 
 sourceSheetSum.Range（D3）。选择
 partName = sourceSheetSum.Range（Selection，Selection.End（xlDown））。 
  

所以我创建了两个不同的表单变量sourceSheets和sourceSheetsSum，如果我使用'sourceSheets'，但是如果我使用'sourceSheetsSum'，则会发生错误1004。我还尝试了完全删除变量'sourceSheet'的代码，以防某些原因覆盖了'sourceSheetSum'。

我相信excel工作簿因为我运行了一些快速的代码来循环遍历工作簿中的所有工作表，并输出名称，如下所示。

 对于j = 1 To sourceBook.Sheets.Count 
 Debug.Print（Sheets（j）.name）
下一步j 
  

调试输出

测量

分析摘要

分析设置

所以，有没有人有任何想法这个错误可能意味着什么，或者我可以如何找到更多关于错误实际是什么？

编辑：
所以我决定添加一个列表的列表名字，不知道是否会有帮助。

 对于j = 1 To sourceBook .Sheets.Count 
 listSheet（j）= Sheets（j）.name 
下一步j 
 Debug.Print（listSheet（2））
设置sourceSheetSum = sourceBook.Sheets（listSheet （2））
  

调试打印分析摘要，所以我知道该工作表存在于工作簿中，名称中的打字错误也不会有任何问题。

尽管如此，代码仍然有相同的错误。

deusxmach1na：我想你想让我改变

  Dim sourceXL As Variant 
 Dim sourceBook As Variant 
 Dim sourceSheet As Variant 
 Dim sourceSheetSum As Variant 
 
设置sourceSheet = sourceBook.Sheets（Measurements）
  

到

  Dim sourceXL As Excel.Application 
 Dim sourceBook As Excel.Workbook 
 Dim sourceSheet As Worksheet 
 Dim sourceSheetSum As工作表
 
设置sourceSheet = sourceBook.Worksheets（Measurements）
  

但是这不会改变错误，我记得我有类似的，然后改变它，因为我看到这个变体就像一个捕获所有，而不是实际上在什么样的变体。

解决方案

您必须先选择工作表才能选择范围。

我已经simp列举了孤立问题的例子。尝试这样：

  Option Explicit 
 
 
 Sub RangeError（）
 
 Dim sourceBook As Workbook 
 Dim sourceSheet As Worksheet 
 Dim sourceSheetSum As Worksheet 
 
设置sourceBook = ActiveWorkbook 
设置sourceSheet = sourceBook.Sheets（Sheet1） 
设置sourceSheetSum = sourceBook.Sheets（Sheet2）
 
 sourceSheetSum.Select 
 
 sourceSheetSum.Range（C3）。选择这是问题线
 
 End Sub 
  

将Sheet1和Sheet2替换为工作表名称。 p>

重要提示：使用变体是危险的，可能导致难以杀死的错误。只有在您有非常具体的理由这样做时才能使用它们。

First time poster, so if there is any formatting, or guidelines I failed to adhere to, please let me know so that I can fix it.

So I am basically asking the user for the file directory of the excel file, then I setup some variables (originally set at public as project variables, since these were being used and changed in other places). I have also added the lines to set these variables to nothing (just in case, I do not think that it should matter). I then set these variables to the excel file, workbook, and sheets that I want to access.

Dim filepath as String
filePath = CStr(fileDialog)              'ask file dir, set to string
Dim sourceXL As Variant                  'these three were orig project variables
Dim sourceBook As Variant
Dim sourceSheet As Variant
Dim sourceSheetSum As Variant

Set sourceXL = Nothing                    'set to nothing in case...?
Set sourceBook = Nothing
Set sourceSheet = Nothing
Set sourceSheetSum = Nothing

Set sourceXL = Excel.Application          'set to the paths needed
Set sourceBook = sourceXL.Workbooks.Open(filePath)
Set sourceSheet = sourceBook.Sheets("Measurements")
Set sourceSheetSum = sourceBook.Sheets("Analysis Summary")

Dim measName As Variant                    'create variable to access later
Dim partName As Variant

sourceSheetSum.Range("C3").Select           'THIS IS THE PROBLEM LINE

measName = sourceSheetSum.Range(Selection, Selection.End(xlDown)).Value
sourceSheetSum.Range("D3").Select
partName = sourceSheetSum.Range(Selection, Selection.End(xlDown)).Value

So I created two different sheet variables 'sourceSheets' and 'sourceSheetsSum', the code works if i use 'sourceSheets', but error 1004 occurs if i use 'sourceSheetsSum'. I have also tried the code with the variable 'sourceSheet' removed completely, in case that was overriding 'sourceSheetSum' for some reason.

I am fairly confident that the excel workbook and sheets exist and are being called correctly, since I ran a quick bit of code to loop through all the sheets in the workbook and output the names, shown below.

For j = 1 To sourceBook.Sheets.Count
Debug.Print (Sheets(j).name)
Next j

With the debug output of

Measurements
Analysis Summary
Analysis Settings

So, does anyone have any ideas what this error could mean, or how I can possibly go about finding more about what the error actually is?

EDIT: So I decided to add a bit to the listing of the sheet names, not sure if it will help at all.

For j = 1 To sourceBook.Sheets.Count
    listSheet(j) = Sheets(j).name
Next j    
Debug.Print (listSheet(2))    
Set sourceSheetSum = sourceBook.Sheets(listSheet(2))

The debug prints Analysis Summary, so I know that the sheet exists in the workbook, and there should not be any issues with a 'typo' in the names.
The code still has the same error at the same line though.

deusxmach1na: I think you wanted me to change

Dim sourceXL As Variant                  
Dim sourceBook As Variant
Dim sourceSheet As Variant
Dim sourceSheetSum As Variant

Set sourceSheet = sourceBook.Sheets("Measurements")

To

Dim sourceXL As Excel.Application
Dim sourceBook As Excel.Workbook
Dim sourceSheet As Worksheet
Dim sourceSheetSum As Worksheet

Set sourceSheet = sourceBook.Worksheets("Measurements")

But this does not change the error, I remember I had it similar to that, and then changed it since I read that variant is like a catch all, not actually that solid on what variant is.

解决方案

You have to select the sheet before you can select the range.

I've simplified the example to isolate the problem. Try this:

Option Explicit


Sub RangeError()

    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim sourceSheetSum As Worksheet

    Set sourceBook = ActiveWorkbook
    Set sourceSheet = sourceBook.Sheets("Sheet1")
    Set sourceSheetSum = sourceBook.Sheets("Sheet2")

    sourceSheetSum.Select

    sourceSheetSum.Range("C3").Select           'THIS IS THE PROBLEM LINE

End Sub

Replace Sheet1 and Sheet2 with your sheet names.

IMPORTANT NOTE: Using Variants is dangerous and can lead to difficult-to-kill bugs. Use them only if you have a very specific reason for doing so.

这篇关于VBA错误1004 - 范围类的选择方法失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！