过程太大 - Excel VBA MAcro

Question

我创建了以下代码，因此每当最终用户从另一个工作表的下拉菜单中选择一周时，它都会更新到RawData中本周的列并更新数字，但似乎我做了一些非常愚蠢的事情，可以使用不同的方式来完成:(

下面是代码

Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
QuestionToMessageBox = "Confirmation Required"
YesOrNoAnswerToMessageBox = MsgBox("Please confirm updating the results for " & Range("F13").Value & " ?", vbYesNo, "Confirmation Required")
If YesOrNoAnswerToMessageBox = vbYes Then
Worksheets("ResultsUpdateView").Activate
If Sheets("ResultsUpdateView").Range("F13").Value = "Week 01" Then
Worksheets("RawData").Activate
With Sheets("RawData")
Sheets("RawData").Range("B2:B14").Value = Sheets("ResultsUpdateView").Range("J40:J52").Value
Sheets("RawData").Range("B16").Value = Sheets("ResultsUpdateView").Range("J55").Value
Sheets("RawData").Range("B19:B28").Value = Sheets("ResultsUpdateView").Range("J58:J67").Value
Sheets("RawData").Range("B32:B41").Value = Sheets("ResultsUpdateView").Range("J71:J80").Value
Sheets("RawData").Range("B45:B54").Value = Sheets("ResultsUpdateView").Range("J84:J93").Value
Sheets("RawData").Range("B58").Value = Sheets("ResultsUpdateView").Range("J97").Value
Sheets("RawData").Range("B59").Value = Sheets("ResultsUpdateView").Range("J98").Value
Sheets("RawData").Range("B62:B71").Value = Sheets("ResultsUpdateView").Range("J101:J110").Value
Sheets("RawData").Range("B75:B84").Value = Sheets("ResultsUpdateView").Range("J114:J123").Value
Sheets("RawData").Range("B88:B97").Value = Sheets("ResultsUpdateView").Range("J127:J136").Value
End With
Worksheets("ResultsUpdateView").Activate
Else
If Sheets("ResultsUpdateView").Range("F13").Value = "Week 02" Then
Worksheets("RawData").Activate
With Sheets("RawData")
Sheets("RawData").Range("C2:C14").Value = Sheets("ResultsUpdateView").Range("J40:J52").Value
Sheets("RawData").Range("C16").Value = Sheets("ResultsUpdateView").Range("J55").Value
Sheets("RawData").Range("C19:C28").Value = Sheets("ResultsUpdateView").Range("J58:J67").Value
Sheets("RawData").Range("C32:C41").Value = Sheets("ResultsUpdateView").Range("J71:J80").Value
Sheets("RawData").Range("C45:C54").Value = Sheets("ResultsUpdateView").Range("J84:J93").Value
Sheets("RawData").Range("C58").Value = Sheets("ResultsUpdateView").Range("J97").Value
Sheets("RawData").Range("C59").Value = Sheets("ResultsUpdateView").Range("J98").Value
Sheets("RawData").Range("C62:C71").Value = Sheets("ResultsUpdateView").Range("J101:J110").Value
Sheets("RawData").Range("C75:C84").Value = Sheets("ResultsUpdateView").Range("J114:J123").Value
Sheets("RawData").Range("C88:C97").Value = Sheets("ResultsUpdateView").Range("J127:J136").Value
End With
Worksheets("ResultsUpdateView").Activate
'--------
Else
If Sheets("ResultsUpdateView").Range("F13").Value = "Week 03" Then
Worksheets("RawData").Activate
With Sheets("RawData")
    Sheets("RawData").Range("D2:D14").Value = Sheets("ResultsUpdateView").Range("J40:J52").Value
    Sheets("RawData").Range("D16").Value = Sheets("ResultsUpdateView").Range("J55").Value
    Sheets("RawData").Range("D19:D28").Value = Sheets("ResultsUpdateView").Range("J58:J67").Value
    Sheets("RawData").Range("D32:D41").Value = Sheets("ResultsUpdateView").Range("J71:J80").Value
    Sheets("RawData").Range("D45:D54").Value = Sheets("ResultsUpdateView").Range("J84:J93").Value
    Sheets("RawData").Range("D58").Value = Sheets("ResultsUpdateView").Range("J97").Value
    Sheets("RawData").Range("D59").Value = Sheets("ResultsUpdateView").Range("J98").Value
    Sheets("RawData").Range("D62:D71").Value = Sheets("ResultsUpdateView").Range("J101:J110").Value
    Sheets("RawData").Range("D75:D84").Value = Sheets("ResultsUpdateView").Range("J114:J123").Value
    Sheets("RawData").Range("D88:D97").Value = Sheets("ResultsUpdateView").Range("J127:J136").Value
End With
Worksheets("ResultsUpdateView").Activate

'__________
Else
If Sheets("ResultsUpdateView").Range("F13").Value = "Week 04" Then
    Worksheets("RawData").Activate
    With Sheets("RawData")
        Sheets("RawData").Range("E2:E14").Value = Sheets("ResultsUpdateView").Range("J40:J52").Value
        Sheets("RawData").Range("E16").Value = Sheets("ResultsUpdateView").Range("J55").Value
        Sheets("RawData").Range("E19:E28").Value = Sheets("ResultsUpdateView").Range("J58:J67").Value
        Sheets("RawData").Range("E32:E41").Value = Sheets("ResultsUpdateView").Range("J71:J80").Value
        Sheets("RawData").Range("E45:E54").Value = Sheets("ResultsUpdateView").Range("J84:J93").Value
        Sheets("RawData").Range("E58").Value = Sheets("ResultsUpdateView").Range("J97").Value
        Sheets("RawData").Range("E59").Value = Sheets("ResultsUpdateView").Range("J98").Value
        Sheets("RawData").Range("E62:E71").Value = Sheets("ResultsUpdateView").Range("J101:J110").Value
        Sheets("RawData").Range("E75:E84").Value = Sheets("ResultsUpdateView").Range("J114:J123").Value
        Sheets("RawData").Range("E88:E97").Value = Sheets("ResultsUpdateView").Range("J127:J136").Value
    End With
    Worksheets("ResultsUpdateView").Activate

Answer 1

尝试在不重复始终相同的内容的情况下构建代码。一种可能的解决方案如下（使用选择大小写和目标列的变量）：

Sub Update()
Dim RawDataSheet As Worksheet, ResUpdSheet As Worksheet, Col As String
Set RawDataSheet = Sheets("RawData")
Set ResUpdSheet = Sheets("ResultsUpdateView")
    If MsgBox("Please confirm updating the results for " & Range("F13").Value & " ?", vbYesNo, "Confirmation Required") = vbNo Then Exit Sub
    Select Case ResUpdSheet.Range("F13").Value
        Case "Week 01"
            Col = "B"
        Case "Week 02"
            Col = "C"
        Case "Week 03"
            Col = "D"
        Case "Week 04"
            Col = "E"
        Case Else
        '...
    End Select
    RawDataSheet.Range(Col & "2:" & Col & "14").Value = ResUpdSheet.Range("J40:J52").Value
    RawDataSheet.Range(Col & "16").Value = ResUpdSheet.Range("J55").Value
    RawDataSheet.Range(Col & "19:" & Col & "28").Value = ResUpdSheet.Range("J58:J67").Value
    RawDataSheet.Range(Col & "32:" & Col & "41").Value = ResUpdSheet.Range("J71:J80").Value
    RawDataSheet.Range(Col & "45:" & Col & "54").Value = ResUpdSheet.Range("J84:J93").Value
    RawDataSheet.Range(Col & "58").Value = ResUpdSheet.Range("J97").Value
    RawDataSheet.Range(Col & "59").Value = ResUpdSheet.Range("J98").Value
    RawDataSheet.Range(Col & "62:" & Col & "71").Value = ResUpdSheet.Range("J101:J110").Value
    RawDataSheet.Range(Col & "75:" & Col & "84").Value = ResUpdSheet.Range("J114:J123").Value
    RawDataSheet.Range(Col & "88:" & Col & "97").Value = ResUpdSheet.Range("J127:J136").Value

End Sub

Answer 2

下面是一个示例。
您可以从单元格 F13 中提取周数，并将其用作列号的基础（第 1 周是 B 列，第 2 周是 C 列）。

colNum = CLng(Replace(Sheets("ResultsUpdateView").Range("F13"), "Week ", "")) + 1 会将您的周数转换为 Long 数据类型，并添加 1 以获取列号。 然后，这可用于引用单元格：Sheets("RawData").Cells(16, colNum)由 colNum 标识的列上的第 16 行。

此代码将从单元格 F13 中获取值，并使用它来将值放在正确的列中（代码未经测试）：

Sub Test()
    Dim wrkSht As Worksheet
    Dim colNum As Long
    Set wrkSht = ThisWorkbook.Worksheets("ResultsUpdateView")
    colNum = CLng(Replace(wrkSht.Range("F13"), "Week ", "")) + 1 'Get the week number from cell F13 & add 1.
    With ThisWorkbook.Worksheets("RawData")
        .Range(.Cells(2, colNum), .Cells(14, colNum)).Value = wrkSht.Range("J40:J52").Value
        .Cells(16, colNum).Value = wrkSht.Range("J55").Value
        .Range(.Cells(19, colNum), .Cells(28, colNum)).Value = wrkSht.Range("J58:J67").Value
        .Range(.Cells(32, colNum), .Cells(41, colNum)).Value = wrkSht.Range("J71:J80").Value
        .Range(.Cells(45, colNum), .Cells(54, colNum)).Value = wrkSht.Range("J84:J93").Value
        .Cells(58, colNum).Value = wrkSht.Range("J97").Value
        .Cells(59, colNum).Value = wrkSht.Range("J98").Value
        .Range(.Cells(62, colNum), .Cells(71, colNum)).Value = wrkSht.Range("J101:J110").Value
        .Range(.Cells(75, colNum), .Cells(84, colNum)).Value = wrkSht.Range("J114:J123").Value
        .Range(.Cells(88, colNum), .Cells(97, colNum)).Value = wrkSht.Range("J127:J136").Value
    End With
End Sub