时间戳

首页 > 解决方案 > Method 'Range' of object'_Worksheet' failed reason? 1

excel - Method 'Range' of object'_Worksheet' failed reason? 1

问题描述

I am writing a code to populate my combobox (Question_Num) using a defined name. My defined name is stored in str3. I'm very new to VBA. When I run the program it gives run time error

method range of object _worksheet failed

I don't know what I am doing wrong

this is where the program hangs...Set rngPage = ws.Range(str3)

Private Sub loadPage()

   Dim rngPage As Range
   Dim ws As Worksheet
   Dim str1 As String, str2 As String, str3 As String

      Set ws = Worksheets(Year_Combine.Value)
      Sheets(Year_Combine.Value).Activate

      str1 = Left(Year_Combine.Value, 4)
      str2 = Right(Year_Combine.Value, 1)
      str3 = "PageNum_" & str1 & "_" & str2

      Set rngPage = ws.Range(str3)

       For Each rngPage In ws.Range(str3)
            Me.Question_Num.AddItem rngPage.Value
       Next rngPage

End Sub

标签： excelvba

解决方案

那是因为你没有给出一个有效的范围。

Range方法接收单元格区域作为参数，例如，如果您要引用工作表中的第一个单元格，则为“A1”，如果您要引用前 2 个水平单元格，则为“A1:B1”。

您可以查看以下链接以获取有关如何使用.Range方法的更多信息。 https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)

推荐阅读