时间戳

首页 > 解决方案 > 在变量中分组值？

vba - 在变量中分组值？

问题描述

如果我有一个文件，我想根据 A 列的值将其剪切成一堆文件，是否可以将某些值放入一个数组中以组合为一个文件——而不是单独的文件？

例如，我想做这样的事情来分组，但是当数组打印到我正在使用的模板时，我不知道如何制作 IF 语句？

  Dim SharedServices As Variant
  SharedServices = "Manager 1, Manager 3, Manager 5, Manager 9"

接着

    If Data(i, 1) <> SharedServices Then

        For sourceCol = 1 To UBound(Data, 2)
          Dest.Offset(destRow, destCol) = Data(sourceRow, sourceCol)
          destCol = destcol + 1
        Next

        destRow = DestRow + 1
    Else
    'Group SharedServices into one file
    End If
    Next

我不太清楚如何将SharedServices数组分组到一个文件中

脚本：

    Option Explicit

Sub Main()
   Dim wb As Workbook
   Dim Data, Last, Login
   Dim sourceRow As Long, destRow As Long, sourceCol As Long, destCol As Long
   Dim Dest As Range
   Dim SharedServices As Variant
   SharedServices = "manager 1, manager 3, manager 7, manager 11"
   Set wb = Workbooks("Template.xlsx")

   Set Dest = wb.Sheets("Full Roster").Range("A3")

   With ThisWorkbook.Sheets("Full Roster")
       Data = .Range("DC3", .Range("A" & Rows.Count).End(xlUp))
   End With
   wb.Activate
   Application.ScreenUpdating = False   
      For sourceRow = 1 To UBound(Data)
        If Data(sourceRow, 1) <> Last Then
          If sourceRow > 1 Then  
            Dest.Select
            wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
            ValidFileName(Last & " - Validation.xlsx")
          End If

          With Sheets("Exempt Population")
             .Rows(3 & ":" & .Rows.Count).ClearContents
          End With

          Last = Data(sourceRow, 1)
          destRow = 0
       End If

       destCol = 0

       If Data(sourceRow, 1) <> SharedServices Then
          For sourceCol = 1 To UBound(Data, 2)
              Dest.Offset(destRow, destCol) = Data(sourceRow, sourceCol)
              destCol = destCol + 1
          Next
          sourceRow = sourceRow + 1
        Else
            'Group SharedServices into one file
        End If
    Next
    SaveCopy wb, Login, Last '<< save the last report        
    End Sub

标签： vba

解决方案

如果您的意思是要检查其中的项目Data是否在您的 SharedServices 列表中，那么一种方法是将其转换为“正确的”数组，即：

Dim SharedServices As Variant

SharedServices = Array("Manager 1", "Manager 3", "Manager 5", "Manager 9")

If IsNumeric(Application.Match(data(i, 1), SharedServices, 0)) Then 'array item is in SharedServices
    'do whatever
End If

推荐阅读