时间戳

首页 > 解决方案 > VBA-遍历单元格数组以获取密码取决于参数

excel - VBA-遍历单元格数组以获取密码取决于参数

问题描述

我想遍历一个充满名称（Merkmal）和密码的列表。

如果我得到正确的名称，则应使用相关密码来编码新的 Excel 文件。

我有两种错误：

  1. 下一个没有 For
  2. 如果没有结束如果
Sub Copy2newfile()
    zeile = 3
    Dim aKopf As Range
    Set aKopf = Range("A1:Y3")
    Dim Password As String

    Do
        ' Bereich kopieren
        altezeile = zeile + 1
        zeile = zeile + 1
        Merkmal = Range("F" & zeile).Value
        Do While Range("F" & zeile).Value = Merkmal
            zeile = zeile + 1
        Loop
        zeile = zeile - 1
        Range("A" & altezeile & ":Y" & zeile).Copy

        Password = generatePassword(Merkmal)


        ' In neue Datei einfügen
        Workbooks.Add
        ActiveSheet.Range("A4").Select
        ActiveSheet.Paste
        ActiveSheet.Range("A1").Select
        aKopf.Copy
        ActiveSheet.PasteSpecial


        'Speichern und schließen
        ActiveWorkbook.SaveAs "C:\Users\1000len-4723\Desktop\Test_" & Merkmal & ".xlsx"
        ActiveWindow.Close
    Loop While Range("F" & zeile + 1).Value <> ""

End Sub

Function generatePassword(Merkmal)
    Dim zeile As Integer
    Dim Password As String
    Dim pswarray As Range
    Dim cell As Range
    Sheets("Tabelle2").Select
    zeile = 2
    Set pswarray = Range("B")

    For Each cell In pswarray
        If cell = Merkmal Then
            Password = ("C" & zeile)
            zeile = zeile + 1
    Next cell

End Function

有人能帮助我吗？

标签： excelvba

解决方案

如果在这里，您将失踪并结束：

    For Each cell In pswarray
        If cell = Merkmal Then
            Password = ("C" & zeile)
            zeile = zeile + 1
        End If '<<---- this was missing.
    Next cell

推荐阅读