excel - 如何根据单元格的值运行代码？

Question

我最近创建了一个基于 for 循环的代码。它读取第一列中每一行的值，并为每一行完成该行的其余部分。

但是，我想根据每行第二列的值来完成该行的其余部分。

实际上，第二列现在的值为“OPEN”或“CLOSED”。如果单元格“打开”，我希望程序运行，如果单元格“关闭”，我想继续下一行。

知道我该怎么做吗？

这是我到目前为止的代码：

Sub StressTest()

    Dim index As Integer
    Dim dateColumn As Integer
    Dim portfolioDate As String
    Dim portfolioName As Variant
    Dim ParametricVar As Double
    Dim AuM As Double
    Dim PreviousVar As Double
    Dim PreviousAuM As Double
    Dim strPath As String
    Dim strFilePath As String
    Dim wb As Workbook
    Dim sheet As Worksheet
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim workB As Workbook
    Dim min As Double
    Dim max As Double


    Set wb = ThisWorkbook
    Set sheet = ActiveSheet


    portfolioDate = InputBox("Please enter date under the following form : YYYY-MM", "Date at the time of Stress Test", "Type Here")
    Debug.Print "Current portfolio date is: " & portfolioDate




      For index = 3 To 37
    If sheet.Cells(index, 3) = "OPEN" Then
                dateColumn = MatchHeader(portfolioDate)
                portfolioName = ActiveSheet.Range("B" & index & "").Value


                strPath = "G:\Risk\Risk Reports\VaR-Stress test\" & portfolioDate & "\" & portfolioName & ""

                Set wb = Workbooks.Open(strPath)

                ParametricVar = Workbooks(portfolioName).Worksheets("VaR Comparison").Range("B19")
                PreviousVar = sheet.Cells(index, dateColumn + 7).Value
                AuM = Workbooks(portfolioName).Worksheets("Holdings - Main View").Range("E11")
                PreviousAuM = sheet.Cells(index, dateColumn + 9).Value


                Set ws1 = wb.Worksheets("Scenarios - Main View")
                Set ws2 = wb.Worksheets("VaR - Main View")
                Set rng2 = ws2.Range("J16:J1000")
                Set rng1 = ws1.Range("11:11")

                max = Application.WorksheetFunction.max(rng2)

                sheet.Cells(index, dateColumn).Value = ParametricVar / AuM
                sheet.Cells(index, dateColumn + 2).Value = AuM
                sheet.Cells(index, dateColumn + 4).Value = ws1.Cells(10, Worst(rng1))
                sheet.Cells(index, dateColumn + 5).Value = Loss(rng1)
                sheet.Cells(index, dateColumn + 6).Value = max
                sheet.Cells(index, dateColumn + 1).Value = (ParametricVar - PreviousVar) / PreviousVar
                sheet.Cells(index, dateColumn + 3).Value = (AuM - PreviousAuM) / PreviousAuM





                wb.Close Savechanges:=False

        Next index

        Else: If sheet.Cells(index, 3) = "CLOSED" Then Next index

End Sub

标签： excelvba

Answer 1

迭代的结构应如下所示：

'First bit of code
For index = 3 To 37
    If Sheet.Cells(index, 3) = "OPEN" then
        'Code
    End if
Next index
'Continue your code