如何在工作簿中的某些但不是所有工作表上运行宏？

I have a workbook that contains worksheets for each industry group in the S&P 500 and wrote the macro below to update all the stock information on them when I press a command button on the first worksheet. The macro works perfectly, but when I go to add additional sheets that I do not want to update with this macro it stops working. I tried using the "If Not" statements below, but it did not seem to work.

我有一个工作簿,其中包含标准普尔500指数中每个行业组的工作表,并在第一个工作表上按下命令按钮时编写下面的宏来更新所有库存信息。宏工作得很好,但是当我去添加我不想用这个宏更新的其他工作表时,它就会停止工作。我尝试使用下面的“If Not”语句,但似乎没有用。

Sub Get_Stock_Quotes_from_Yahoo_Finance_API()

'Run the API for every sheet in the workbook
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets

    'Look to see what the sheet is named and run the macro if it is not what is below
    If Not Sht.Name = "Cover" _
    And Not Sht.Name = "Select Industry" Then

    Sht.Activate

        ' Dim varibales and set range
        Dim head As Range
        Set head = Worksheet.Range("A2")

        'dim variables
        Dim I As Integer
        Dim Symbols As String: Symbols = ""
        Dim SpecialTags As String: SpecialTags = ""
        Dim Yahoo_Finance_URL As String: Yahoo_Finance_URL = "http://finance.yahoo.com/d/quotes.csv?s="
        Dim rng As Range
        Dim cell As Range

        ' Get the Stock Symbols
        Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
        For Each cell In rng ' Starting from a cell below the head cell till the last filled cell
            Symbols = Symbols & cell.Value & "+"
        Next cell
        Symbols = Left(Symbols, Len(Symbols) - 1) ' Remove the last '+'

        ' Get the Special Tags
        Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
        For Each cell In rng ' Starting from a cell to the right of the head cell till the last filled cell
            SpecialTags = SpecialTags & cell.Value
        Next

        ' Put the desciption/name of each tag in the cell above it
        Dim SpecialTagsArr() As String: Dim TagNamesArr() As String
        Call Get_Special_Tags(SpecialTagsArr, TagNamesArr)
        For Each cell In rng
            cell.Offset(-1, 0).Value = FindTagName(cell.Value, SpecialTagsArr, TagNamesArr)
        Next


        Yahoo_Finance_URL = Yahoo_Finance_URL & Symbols & "&f=" & SpecialTags

        Call Print_CSV(Yahoo_Finance_URL, head)

Next Sht
'At the end of the program say it has all been updated
MsgBox ("All Data Updated")
End Sub

2 个解决方案

#1

Change

 If Not Sht.Name = "Cover" _
    And Not Sht.Name = "Select Industry" Then

If Sht.Name <> "Cover" And Sht.Name <> "Select Industry" Then

Don't forget your End If before Next Sht

不要忘记你的结束如果在下一个Sht之前

#2

Refering to Kevin's second code - now the exclusion logic is flawed. I suggest the following:

参考Kevin的第二个代码 - 现在排除逻辑是有缺陷的。我建议如下:

Function IsIn(element, arr) As Boolean
    IsIn = False
    For Each x In arr
        If element = x Then
            IsIn = True
            Exit Function
        End If
    Next x
End Function


Sub Get_Stock_Quotes_from_Yahoo_Finance_API()
    Dim skippedSheets()
    skippedSheets = Array("Cover,Select Industry,bla bla")

    For Each Sh In ActiveWorkbook.Worksheets
        If Not IsIn(Sh.Name, skippedSheets) Then
            ' ... process Sh

        End If
    Next Sh
End Sub

Now you have all sheet names which are to be excluded in one place (the array assignment) and the inner code block will only be executed if the current sheet name is not element of that array.

现在,您已将所有工作表名称排除在一个位置(数组赋值),并且只有当前工作表名称不是该数组的元素时才会执行内部代码块。

Second source of error: you already started qualifying the ranges (like in Set head = Sht.Range("A2")). Do the same in 2 other places, with
Set rng = Sht.Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
and
Set rng = Sht.Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))

第二个错误来源:您已经开始限定范围(例如Set head = Sht.Range(“A2”))。在其他2个地方做同样的事情,使用Set rng = Sht.Range(head.Offset(1,0),head.Offset(1,0).End(xlDown))和Set rng = Sht.Range(head.Offset) (0,1),head.Offset(0,1).End(xlToRight))

Last, you don't have to activate a sheet. You work with the Sht object and qualified ranges.
Dim I as Integer is unused.

最后,您不必激活工作表。您使用Sht对象和限定范围。 Dim I as Integer未使用。

#1