VBA:循环中的相对单元格引用 [英] VBA: relative cell reference in loop

问题描述

我正在尝试在循环中使用相对引用.我的目标是使用索引和匹配组合来查找独立于文件中有多少行和多少列的值.这是我到目前为止创建的，但是没有用.数据是相同的，存储在两个不同的表中.

I am trying to use relative reference in a loop. My aim is to use index and match combined to find values independent of how many rows and columns are present in the file. This is what I have created so far, but it does not work. The data is the the same stored on two different sheets.

 Error msg: Run-time error '1004':

无法获取WorksheetFunction类的Match属性

Unable to get the Match Property of the WorksheetFunction class

1. 子测试()

1. Sub testing()

Dim ActSor As Long, ActOsz As Long

ActSor = ActiveSheet.UsedRange.Rows.Count 
ActOsz = ActiveSheet.UsedRange.Columns.Count

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Dim Rg As Range

Set Rg = ws2.Range("B2", Cells(ActSor, ActOsz))

Dim RgActOsz As Long

RgActOsz = Rg.Columns.Count

Dim i As Long , sor As Long

For i = 2 To RgActOsz
   For sor = 2 To ActSor
      Cells(sor, i).Value = Application.WorksheetFunction.Index(ws1.Columns(i),
      Application.WorksheetFunction.Match(Cells(sor, 1), Rg.Columns(1), 0))
   Next sor
Next i

End Sub

推荐答案

Set Rg = ws2.Range("B2", Cells(ActSor, ActOsz))

这是禁止的.正确的语法:

this is forbidden. Correct syntax:

Set Rg = Range( ws2.Range("B2"), ws2.Cells(ActSor, ActOsz))

有帮助吗?

这篇关于VBA:循环中的相对单元格引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！