☰

Excel VBA 代码将具有可变列数的行转换为固定长度的行

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2013
Excel VBA 代码将具有可变列数的行转换为固定长度的行

我想改变这一点：

Filer   ShareName       IPAddress1    IPpaddres2    IPAddress2    ... ... ....
ServerA /share1/tools   192.168.1.52  192.168.1.65  192.168.1.158
ServerA /share/library  192.168.1.65  192.168.1.61  192.168.1.155
ServerB /share/tools    192.168.1.158 192.168.1.159
ServerD /share/misc     192.168.1.7
..
...
.....
.......

为此，在新的工作表中：

Filer   ShareName       IPAddress
ServerA /share1/tools   192.168.1.52
ServerA /share1/tools   192.168.1.65
ServerA /share1/tools   192.168.1.158
ServerA /share/library  192.168.1.65 
ServerA /share/library  192.168.1.61 
ServerA /share/library  192.168.1.155
ServerB ... ...
ServerD ... ...
..
...
.....
.......

答案1

  1. 前往开发人员选项卡然后点击“录制宏”
  2. 然后添加新表
  3. 停止录音
  4. 查看单击“Visual Basic”生成的代码，它可能位于“模块 1”中

对下一个问题重复此操作并使用此代码作为您要构建的基础。

当你遇到困难时，用 Google 搜索你的问题。如果这不能解决问题，请在此处发布你的代码，并明确说明你想要它做什么、你认为它做什么以及你认为哪里是错误的或不理解的。

答案2

VBA/宏中的代码：

Public Sub distribute()
    Application.ScreenUpdating = False
    Dim wkb As Workbook
    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    Set wkb = ThisWorkbook
    Set wks1 = wkb.Worksheets("Sheet1")
    Set wks2 = wkb.Worksheets("Sheet2")
    wks2.Rows.Clear
    therow = 1
    othersheetrow = 1
    morerows = True
    While morerows
        morecolumns = True
        thecolumn = 3
        abscolumn = thecolumn
        While morecolumns
            a = wks1.Cells(therow, thecolumn)
            If (a = "") And (thecolumn = abscolumn) Then
                morerows = False
                morecolumns = False
            ElseIf (a = "") And (thecolumn > abscolumn) Then
                morecolumns = False
                therow = therow + 1
            Else
                If (therow = 1) Then
                    For i = 1 To abscolumn
                        wks2.Cells(therow, i) = wks1.Cells(therow, i)
                    Next i
                    therow = therow + 1
                    thecolumn = thecolumn - 1
                Else
                    othersheetrow = othersheetrow + 1
                    For i = 1 To abscolumn - 1
                        wks2.Cells(othersheetrow, i) = wks1.Cells(therow, i)
                    Next i
                    wks2.Cells(othersheetrow, i) = wks1.Cells(therow, thecolumn)
                End If
            End If
            thecolumn = thecolumn + 1
        Wend
    Wend
    Application.ScreenUpdating = True
    themessage = MsgBox("Finished", vbInformation)
End Sub

打开 VBA / Macro con ALT+ F11，然后在下方插入一个新模块本工作簿并将代码粘贴到右侧。

有一个名为的变量thecolumn在 3 上初始化，这意味着它是在 Sheet2 上创建新条目（行）之前要考虑的第一列。

相关内容