时间戳

首页 > 解决方案 > 需要一个更整洁的解决方案

excel - 需要一个更整洁的解决方案

问题描述

'这是一个 Excel VBA 程序的摘录，用于从名为事件日历的表中提取“天数”和“-1,0,+1”标志。第 1 列 - 事件“x”，第 2 列月份的天数，第 3 列 -1,0 或 +1。'事件没有被描述为事件 1、事件 2 等，但有各种（固定的）描述。'“活动单元格”是我的工作簿第 1 页中的日期（第 1）列，“事件 x”是同一页面上的描述（第 3）列。'事件日历是我的工作簿第 2 页中的 3 列表'第 580 行检查“新”日期是否是周末或银行假日并适当调整，一些通过增加天数，另一些通过减去天数来确定“标志”设置为选择的数字之一（-1,0 或 +1）。“标志”由“ Flag = "，也由Events.Column 3的Table Calender确定。' “这很“笨拙”，必须有一个更整洁的方式。

    Dim Calender_of_Events(1 To 100, 1 To 2) As Variant   'Array to hold the Dates of the Calender of Events.
    'Dim x, Dim u etc

Windows("Calender of Events.xlsm").Activate
Sheets("Page 2").Select

    'Populate Calender_of_Events

x = Range("W2").Value                                                   'No. of Lines in the Table (Page 2 - Column W (Col 23))
ActiveSheet.Cells(2, 23).Select

    For u = 1 To (x + 1)
        ActiveCell.Offset(1).Select
        Calender_of_Events(u, 1) = ActiveCell()
        Calender_of_Events(u, 2) = ActiveCell.Offset(0, -1)
    Next u


Windows("Calender of Events.xlsm").Activate
Sheets("Page 1").Select


Line260:
    If (ActiveCell.Offset(0, 2) = "Event 1") Then Else GoTo Line270                                            'Not "Event 1" then go to the next detail
        If (IsError(Application.VLookup("Event 1", Calender_of_Events, 2, False))) Then GoTo Line990
            A234 = Application.VLookup("Event 1", Calender_of_Events, 2, False)                                                           '"No Match" Else "Match"
            C210 = CDate((A234) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line270:
    If (ActiveCell.Offset(0, 2) = "Event 2") Then Else GoTo Line280                           'Not "Event 2" then go to the next detail
        If (IsError(Application.VLookup("Event 2", Calender_of_Events, 2, False))) Then GoTo Line990                           '"No Match" Else "Match"
            A244 = Application.VLookup("Event 2", Calender_of_Events, 2, False)
            C210 = CDate((A244) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line280:
    If (ActiveCell.Offset(0, 2)) = "Event 3" Then Else GoTo Line290       'Not "Event 3" then go to the next detail
        If (IsError(Application.VLookup("Event 3", Calender_of_Events, 2, False))) Then GoTo Line990       '"No Match" Else "Match"
            A253 = Application.VLookup("Event 3", Calender_of_Events, 2, False)
            C210 = CDate((A253) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line290:
    If (ActiveCell.Offset(0, 2)) = "Event 4" Then Else GoTo Line300             'Not "Event 4" then go to the next detail
        If (IsError(Application.VLookup("Event 4", Calender_of_Events, 2, False))) Then GoTo Line990   '"No Match" Else "Match"
            A259 = Application.VLookup("Event 4", Calender_of_Events, 2, False)
            C210 = CDate((A259) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line300:
    If (ActiveCell.Offset(0, 2)) = "Event 5" Then Else GoTo Line310        'Not "Event 5" then go to the next detail
        If (IsError(Application.VLookup("Event 5", Calender_of_Events, 2, False))) Then GoTo Line990   '"No Match" Else "Match"
            A265 = Application.VLookup("Event 5", Calender_of_Events, 2, False)
            C210 = CDate((A265) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line310:
    If (ActiveCell.Offset(0, 2)) = "Event 6" Then Else GoTo Line320                                   'Not "Event 6" then go to the next detail
        If (IsError(Application.VLookup("Event 6", Calender_of_Events, 2, False))) Then GoTo Line990                         '"No Match" Else "Match"
            A270 = Application.VLookup("Event 6", Calender_of_Events, 2, False)
            C210 = CDate((A270) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line320:
    If (ActiveCell.Offset(0, 2)) = "Event 7" Then Else GoTo Line330                               'Not "Event 7" then go to the next detail
        If (IsError(Application.VLookup("Event 7", Calender_of_Events, 2, False))) Then GoTo Line990                     '"No Match" Else "Match"
            A275 = Application.VLookup("Event 7", Calender_of_Events, 2, False)
            C210 = CDate((A275) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line330:
    If (ActiveCell.Offset(0, 2)) = "Event 8" Then Else GoTo Line340                                 'Not "Event 8" then go to the next detail
        If (IsError(Application.VLookup("Event 8", Calender_of_Events, 2, False))) Then GoTo Line990  '"No Match" Else "Match"
            A280 = Application.VLookup("Event 8", Calender_of_Events, 2, False)
            C210 = CDate((A280) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line340:
    If (ActiveCell.Offset(0, 2)) = "Event 9" Then Else GoTo Line350                                  'Not "Event 9" then go to the next detail
        If (IsError(Application.VLookup("Event 9", Calender_of_Events, 2, False))) Then GoTo Line990                        '"No Match" Else "Match"
            A285 = Application.VLookup("Event 9", Calender_of_Events, 2, False)
            C210 = CDate((A285) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

Line350:
    If (ActiveCell.Offset(0, 2)) = "Event 10" Then Else GoTo Line360                          'Not "Event 10" then go to the next detail
        If (IsError(Application.VLookup("Event 10", Calender_of_Events, 2, False))) Then GoTo Line990     '"No Match" Else "Match"
            A294 = Application.VLookup("Event 10", Calender_of_Events, 2, False)
            C210 = CDate((A294) & "/" & (A174) & "/" & (Input_Year))
            ActiveCell.Value = C210
            C202 = ActiveCell()
            z = 0: GoTo Line580

标签： excelvba

解决方案

我真的很抱歉发布这个。答案基本上如此简单，我不太明白为什么我一开始看不到它。也许是因为我来自一个经常使用 Macro 的时代。VBA 更加灵活和强大，我仍在为一些可用的功能而苦苦挣扎（比使用宏的功能要多得多）。如果版主没有异议，我想从论坛中删除这个“问题”——或者他们可以为我做。谢谢

推荐阅读