vba - 循环遍历工作簿并获取一个值(如果存在于单元格中)

Question

我对 VBA/宏还很陌生，并设法获得了 Worksheet名字进入我的ActiveWorkbook .我现在正在尝试检查特定的 Value存在于 Cell 中然后将其放在 Worksheet 旁边的 B 列中我已经提取的名称。只有特定工作表的值是 1-54，因为它是 IED 设备的地址，而且它只是我关心的工作表。

目前我正在使用此代码来提取 Worksheet名称，因此我可以继续使用此引用来提取一系列信息，因为我无法正确引用工作表编号或工作表代码名称。

 Sub GetSheetnames()
 'Turn off alerts
      Application.DisplayAlerts = False

  'Open the Workbook that i want to look for the worksheet names this 
  'white book template is a save as copy of another file as it makes it 
  'easier to reference as I work with 1000's of file with same format but 
   'are all saved as a different name

  Workbooks.Open Filename:="D:\Projects\ASE Templates\ASE Template White 
  Book.xlsx"
  Dim ws As Worksheet
  Dim i As Integer
  With ThisWorkbook.Worksheets("Tab Names from white book")
  .Range("A:A").ClearContents
  For Each ws In ActiveWorkbook.Worksheets
  i = i + 1
 .Range("A" & i) = ws.Name

 Next ws

End With

 Workbooks("ASE Template White Book.xlsx").Close

  Application.DisplayAlerts = True

 End Sub

我还想添加的是检查 H4 中的值是否在每个 Worksheet是否存在，如果存在，则在我的工作表“白皮书中的选项卡名称”的 A 列中提取的工作表名称旁边显示 B 列中的值

我确实看过重新运行宏来改变

With ThisWorkbook.Worksheets("Tab Names from white book")
   .Range("A:A").ClearContents
   For Each ws In ActiveWorkbook.Worksheets
       i = i + 1
      .Range("A" & i) = ws.Name
   Next ws

至

With ThisWorkbook.Worksheets("Tab Names from white book")
    .Range("B:B").ClearContents
    For Each ws In ActiveWorkbook.Worksheets
       i = i + 1
       .Range("B" & i) = H4.value

或者

.Range("B" & i) = Cells(3,8).Value

这会运行，但不会做任何可见的事情。任何帮助，将不胜感激。理想情况下，我想运行一次宏并同时填写两列

Answer 1

在我看来，好像您没有限定您想要获取的单元格范围，并且 Set也建议您将工作簿放在后面以便于引用。

Sub GetSheetnames()
'Turn off alerts
Application.DisplayAlerts = False

'Open the Workbook that i want to look for the worksheet names this
'white book template is a save as copy of another file as it makes it
'easier to reference as I work with 1000's of file with same format but
'are all saved as a different name

Dim wb As Workbook
Set wb = Workbooks.Open("D:\Projects\ASE Templates\ASE Template White Book.xlsx")

Dim ws As Worksheet
Dim i As Integer
With ThisWorkbook.Worksheets("Tab Names from white book")
    .Range("A:A").ClearContents
    For Each ws In wb.Worksheets
        i = i + 1
        .Range("A" & i) = ws.Name
        .Range("B" & i) = ws.Cells(3, 8).Value
    Next ws
End With

wb.Close

Application.DisplayAlerts = True

End Sub