时间戳

首页 > 解决方案 > 有没有一种简单的方法可以使用 case 语句存储已保存的文件？

excel - 有没有一种简单的方法可以使用 case 语句存储已保存的文件？

问题描述

我正在尝试创建代码来检查一列名称。我想将文件分为三类（名称介于 AG、HP、QZ 之间）。

在保存文件之前，我认为 case 语句是正确的方法，但不确定我是否需要使用instr函数或类似的东西。

这是我当前如何设置它的粗略示例（注释掉案例陈述）。

        Dim BASEPATH_1 As String, BASEPATH_2 As String, BASEPATH_3 As String


        BASEPATH_1 = "C:\Users\A-G\"
        BASEPATH_2 = "C:\Users\H-P\"
        BASEPATH_3 = "C:\Users\Q-Z\"


        Select Case wb.Cells(i, 8)
        'Case i.value is betwen "A-G"
            wb.SaveCopyAs BASEPATH_1 & _
            ValidFileName(Login & "_" & Last & "_PrePlanning File.xlsx")
        'Case i.value is betwen "H-P"
            wb.SaveCopyAs BASEPATH_2 & _
            ValidFileName(Login & "_" & Last & "_PrePlanning File.xlsx")
        'Case i.value is betwen "Q-Z"
            wb.SaveCopyAs BASEPATH_3 & _
            ValidFileName(Login & "_" & Last & "_PrePlanning File.xlsx")
        Case Else
        End Select

完整代码：

Sub Main()
    Dim wb As Workbook
    Dim Data, Last, Login, lvl2mgr
    Dim i As Long, j As Long, k As Long, a As Long
    Dim Dest As Range
    Dim BASEPATH1 As String, BASEPATH2 As String, BASEPATH3 As String, strNewPath As String


    BASEPATH1 = "C:\A-G"
    BASEPATH2 = "C:\H-P"
    BASEPATH3 = "C:\Q-Z"

    Set wb = Workbooks("Preplanning_Template.xlsx")

    Set Dest = wb.Sheets("Manager File").Range("A3")

    With ThisWorkbook.Sheets("Planning File")
        Data = .Range("BP2", .Range("A" & Rows.Count).End(xlUp))
    End With

    wb.Activate
    Call Ludicrous(True)

    For i = 1 To UBound(Data)

        If Data(i, 7) <> Login Then
            If i > 1 Then
                Dest.Select
                wb.Sheets(1).Cells.WrapText = False
                    Call FillDown
                    Call FillColors
                wb.Cells.Columns("A:BP").EntireColumn.AutoFit
                wb.Cells.HorizontalAlignment = xlLeft
                wb.Columns("E:F").EntireColumn.Hidden = True
                ActiveSheet.Outline.ShowLevels ColumnLevels:=1
            End If

           Select Case Asc(Cells(i, 8).Value)

                Case 65 To 71 'A-G
                    wb.SaveCopyAs BASEPATH1 & _
                    ValidFileName(Login & "_" & Last & "_PrePlanning File.xlsx")

                Case 72 To 80 'H-P
                    wb.SaveCopyAs BASEPATH2 & _
                    ValidFileName(Login & "_" & Last & "_PrePlanning File.xlsx")

                Case 81 To 90 'Q-Z
                    wb.SaveCopyAs BASEPATH3 & _
                    ValidFileName(Login & "_" & Last & "_PrePlanning File.xlsx")

            Case Else
            End Select

            With wb.Sheets("Manager File")
                .Rows(3 & ":" & .Rows.Count).ClearContents
                .Rows(3 & ":" & .Rows.Count).Interior.Color = xlNone
            End With

            Login = Data(i, 7)
            Last = Data(i, 8)


            j = 0
        End If

        a = 0
        For k = 1 To UBound(Data, 2)
            Dest.Offset(j, a) = Data(i, k)
            a = a + 1
        Next
        j = j + 1
    Next

    SaveCopy wb, Login, Last

    Call Ludicrous(False)

End Sub

标签： excelvba

解决方案

在此处使用Asc，来自文档：

返回一个Integer表示字符串中第一个字母对应的字符代码

Select Case Asc(wb.Cells(i, 8).Value)
    Case 65 to 71 'A to G
        ...
    Case 72 to 80 'H to P
        ...
    Case 81 to 90 'Q to Z
        ...
End Select

请注意，您应该检查单元格是否也不是空的。把这个包起来If Not IsEmpty(wb.Cells(i, 8).Value) Then...End If

第二个注意事项，wb如果这确实是一个Worksheet变量，则非常具有误导性 - 尝试ws。如果wb是 a Workbook，那么您之前需要一个工作表引用Cells。

推荐阅读