excel - 在 VBA 中转置和格式化数据，需要帮助确保我的数据在正确的工作表上被编辑

Question

这应该是一个容易解决的问题，但我对 VBA 非常不熟悉，所以我需要一些帮助。

本质上，我想复制给我的数据（有限的列范围，但动态行），并将其粘贴到 Excel 中的第二张表中。从那里，我需要将 D、E、F 和 H 列中的所有值转换为 12 中的分数。我很确定我已经完成了 99%，但最后一步（转换为分数文本) 在错误的工作表中执行。我试过使用 .Activate 函数以及设置正确的工作表，但都不起作用。我错过了一个简单的解决方法。注意：这是我第一次在 VBA 中编码，所以请放轻松。代码如下：

Option Explicit

Sub FormatData()

Dim ws As Worksheet

' create a new worksheet, and name it "Master"
Set ws = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(1))
ws.Name = "Master"

' copy the UsedRange and Transpose
Worksheets("Sheet1").UsedRange.Copy
ws.Range("A1").PasteSpecial xlPasteAll, Transpose:=True

Sheets("Master").Activate

Dim i As Long, N As Long, j As Long
N = Cells(Rows.Count, "D").End(xlUp).Row
j = 2
For i = 2 To N
    Cells(i, "D") = WorksheetFunction.Text(Cells(i, "D"), "0 0/12")
    j = j + 1
    Next i
j = 2
For i = 2 To N
    Cells(i, "E") = WorksheetFunction.Text(Cells(i, "E"), "0 0/12")
    j = j + 1
    Next i
j = 2
For i = 2 To N
    Cells(i, "F") = WorksheetFunction.Text(Cells(i, "F"), "0 0/12")
    j = j + 1
    Next i
j = 2
For i = 2 To N
    Cells(i, "H") = WorksheetFunction.Text(Cells(i, "H"), "0 0/12")
    j = j + 1
    Next i

End Sub

标签： excelvba

Answer 1

首先只做一个循环并在那个循环内完成所有四个转换。其次，j 什么都不做，可以省略。

Sub FormatData()

    Dim ws As Worksheet

    ' create a new worksheet, and name it "Master"
    Set ws = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(1))


    With ws
        .Name = "Master"

        ' copy the UsedRange and Transpose
        Worksheets("Sheet1").UsedRange.Copy
        .Range("A1").PasteSpecial xlPasteAll, Transpose:=True

        Dim i As Long, N As Long
        N = .Cells(.Rows.Count, "D").End(xlUp).Row

        For i = 2 To N
            .Cells(i, "D") = Application.WorksheetFunction.Text(.Cells(i, "D"), "# ??/12")
            .Cells(i, "E") = Application.WorksheetFunction.Text(.Cells(i, "E"), "# ??/12")
            .Cells(i, "F") = Application.WorksheetFunction.Text(.Cells(i, "F"), "# ??/12")
            .Cells(i, "H") = Application.WorksheetFunction.Text(.Cells(i, "H"), "# ??/12")
        Next i
    End With

End Sub