excel - 如何获取 Excel 表中所有标记行的地址、列名和行名作为新工作表中的行

Question

我需要标有“”的行/列组合X ' 在我的表格中可用作另一张表中的三列。

第一列将包含 小区地址 ,

第二列将有 行名 , 和

第三列将有 栏目名称标记的单元格。

VLookUp和 Index/ Match没有帮助。

预期结果:

Answer 1

您可能会摆脱一些懒惰的事情，您会根据需要更改工作表和目标范围 srcSht.Range("A1:C5") :

Option Explicit

Sub test()

Dim wb As Workbook

Dim srcSht As Worksheet
Dim destSht As Worksheet

Set wb = ThisWorkbook
Set srcSht = wb.Sheets("Sheet1")
Set destSht = wb.Sheets("Sheet2")

Dim targetRange As Range

Set targetRange = srcSht.Range("A1:C5")

Dim loopArray()
loopArray = targetRange.Value2

Dim currRow As Long
Dim currCol As Long
Dim counter As Long

For currRow = LBound(loopArray, 1) To UBound(loopArray, 1)

    For currCol = LBound(loopArray, 2) To UBound(loopArray, 2)

        If LCase$(loopArray(currRow, currCol) )= "x" Then
            counter = counter + 1
            destSht.Cells(counter, 1) = targetRange.Cells(currRow, currCol).Address
            destSht.Cells(counter, 2) = "Column " & targetRange.Cells(currRow, currCol).Column
             destSht.Cells(counter, 3) = "Row " & targetRange.Cells(currRow, currCol).Row

        End If

   Next currCol

Next currRow


End Sub