过程太大- 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

试着组织你的代码，而不是重复那些总是相同的东西。一种可能的解决方案可能如下所示(使用select case和目标列的变量)：

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以获得列数。然后可以用它来引用一个单元格:由colNum标识的列上的第16行的Sheets("RawData").Cells(16, colNum)。

此代码将从单元格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