excel - 将字符串的一部分设置为百分比

Question

一直试图让一个 msgbox 显示用户可以看到和使用的一系列单元格。这需要是我的工作簿另一张工作表中两列的列表。完成了大部分代码，但我得到了一个我无法理解的输出。

在代码中，我希望NCol在 msgbox 提示时显示为百分比。

第一个想法是将该变量中Format(NCol,"0.00%")的每个单元格设置为For NCol = ....

第二个想法在里面Str & Rng.Cells...Format(Ncol..，显然没有如愿以偿。

任何指向正确方向的指针都非常感谢。下图显示了 msgbox 中的结果。输入

结果

Sub ShowTopCat()
    Dim Rng As Range
    Dim ACell As Range
    Dim Str As String
    Dim ARow As Long
    Dim NCol As Long
    Dim art As Worksheet
    Set art = Worksheets("Sheet1")
    On Error Resume Next
    Set Rng = art.Range("x2:y101")
    If Rng Is Nothing Then Exit Sub
    On Error Resume Next
    For ARow = 1 To Rng.Rows.Count
        For NCol = 1 To Rng.Columns.Count
                Str = Str & Rng.Cells(ARow, NCol).Value & vbTab
        Next
        Str = Str & vbCrLf
    Next
    MsgBox Str, vbInformation, "You top cats"
End Sub

标签： excelvbamsgbox

Answer 1

当您的范围固定时，为什么要使用两个循环？

Sub ShowTopCat()
    Dim art As Worksheet
    Dim Rng As Range
    Dim Str As String
    Dim ARow As Long

    Set art = Worksheets("Sheet1")

    On Error Resume Next
    Set Rng = art.Range("x2:y101")
    If Rng Is Nothing Then Exit Sub
    On Error Resume Next

    For ARow = 1 To Rng.Rows.Count
        With Rng.Cells(ARow, 1)
            If .Value2 <> vbNullString Then
                Str = Str & .Value2 & vbTab & Format(.Offset(0, 1).Value2, "0" & Application.DecimalSeparator & "00%")
                Str = Str & vbCrLf
            End If
        End With
    Next ARow
    MsgBox Str, vbInformation, "You top cats"
End Sub