时间戳

首页 > 解决方案 > 将行中的数据与子数据对齐

excel - 将行中的数据与子数据对齐

问题描述

我有两列数据，它们是两种不同型号交换机的交换机配置。我想根据接口名称对齐数据并插入空格以匹配。我已经看到了很多对齐行的示例，但没有一个示例与下面的后续数据不需要被视为专门对齐。

老的：

int g1/0/1        int g1/0/1
desc new text     desc old text
…                 …
…                 …
…                 …
…   
…                 int g1/0/2
                  desc old text2
int g1/0/2        …
desc new text2    …
…                 …
…   
…                 int g1/0/3
…                 desc old text3
…                 …
                  …
int g1/0/3        …
desc new text3  
…   
…   
…   
…   
…

新的：

int g1/0/1      int g1/0/1
desc new text   desc old text
…               …
…               …
…               …
…   
…

int g1/0/2      int g1/0/2
desc new text2  desc old text2
…               …
…               …
…               …
…   
…               

int g1/0/3      int g1/0/3
desc new text3  desc old text3
…               …
…               …
…               …
…   
…

更新：

这是我根据以下建议提出的代码。

    Sub Interface_Align()

Dim ws As Worksheet, Xstr As String, Xstr2 As String
Set ws = ActiveWorkbook.ActiveSheet
Dim Rw As Long, Rw2 As Long, SRow As Long, ERow As Long, i As Long, FCol As Long, SCol As Long
Dim Rng1 As Range, Rng2 As Range, C As Range, D As Range


SRow = 1
ERow = 1100
FCol = ActiveCell.Column
SCol = FCol + 1

Set Rng1 = ws.Range(Cells(SRow, FCol), Cells(ERow, FCol))
Set Rng2 = ws.Range(Cells(SRow, SCol), Cells(ERow, SCol))

    For Rw = SRow To ERow

        Xstr = Rng1.Cells(Rw, 1).Text
        If Left(Xstr, 25) = "interface GigabitEthernet" Then

            Set C = Rng2.Find(Xstr)

            If Not C Is Nothing Then
                If C.Row < Rw Then
                    For i = 1 To Rw - C.Row
                        C.Insert xlShiftDown
                    Next
                ElseIf C.Row > Rw Then
                    For i = 1 To C.Row - Rw
                        C.Offset(-1, 0).Delete xlShiftUp
                    Next
                End If

            End If
            For Rw2 = (C.Row + 1) To ERow

                Xstr2 = Rng2.Cells(Rw2, 1).Text

                If Left(Xstr2, 25) = "interface GigabitEthernet" Then

                    Set D = Rng1.Find(Xstr2)

                    If Not D Is Nothing Then

                        If Rw2 > D.Row Then
                            For i = 1 To (Rw2 - D.Row)
                                D.Insert xlShiftDown
                            Next
                            Exit For
                        End If

                    End If

                End If

            Next

        End If

    Next


End Sub

标签： excelvba

解决方案

可以尝试这样的事情 数据假定在 Coulmn A & B 中。在重要数据之间的单元格之间假定为空白（或 3 个点 - 如图所示）。根据您的要求修改行列范围等

Sub test()
Dim ws As Worksheet, Xstr As String
Set ws = ThisWorkbook.ActiveSheet
Dim Rw As Long, SRow As Long, ERow As Long, i As Long
Dim Rng As Range, C As Range

SRow = 1
ERow = 100
Set Rng = ws.Range(Cells(SRow, 2), Cells(ERow, 2))

    For Rw = SRow To ERow
    Xstr = ws.Cells(Rw, 1).Value
        If Xstr <> "" And Xstr <> "..." Then
        Set C = Rng.Find(Xstr)
            If Not C Is Nothing Then
                If C.Row < Rw Then
                    For i = 1 To Rw - C.Row
                    C.Insert xlShiftDown
                    Next
                ElseIf C.Row > Rw Then
                    For i = 1 To C.Row - Rw
                    C.Offset(-1, 0).Delete xlShiftUp
                    Next
                End If
            End If
        End If
    Next
End Sub

推荐阅读