excel - 从列中选择状态以填充另一列

Question

我有一个电子表格，每天都会更新不同破产状态的公司列表 (**text**)。这些状态可能会有所不同，并且可以添加新状态。

宏必须做的是填充column A与公司目前的破产状态，并将其从column B中删除.一个例子，之前:

column A    column B
            **Bankruptcy Required**
            Company 1
            Company 2
            Company 3
            **Bankruptcy Decreed**
            Company 4
            Company 5
            **Extinct Bankruptcy Process**
            Company 6
            **Required Reorganization**
            Company 9
            Company 10
            Company 11
            **Judicial Recovery Upheld**
            Company 12
            Company 14
            Company 15
            Company 16

后:

column A                          column B
Bankruptcy Required               Company 1
Bankruptcy Required               Company 2
Bankruptcy Required               Company 3
Bankruptcy Decreed                Company 4
Bankruptcy Decreed                Company 5
Extinct Bankruptcy Process        Company 6
Required Reorganization           Company 9
Required Reorganization           Company 10
Required Reorganization           Company 11
Judicial Recovery Upheld          Company 12
Judicial Recovery Upheld          Company 14
Judicial Recovery Upheld          Company 15
Judicial Recovery Upheld          Company 16

有任何想法吗？

Answer 1

遍历行并检查 B 列中的内容。

Private Sub FixData()
    Dim ws As Excel.Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet2")
    Dim lastRow As Long
    Dim szStatus As String
    Dim lrow As Long
    lrow = 1

    lastRow = ws.Cells(ws.Rows.count, "B").End(xlUp).Row

    'Loop through the rows
    Do While lrow <= lastRow
        'Check if the value in column B is bold
        If ws.Range("B" & lrow).Font.Bold = True Then
            'Get the value
            szStatus = Trim(ws.Range("B" & lrow).Value)
            'Delete that row
            ws.Rows(lrow).EntireRow.Delete
        Else
            'If it does not have ** in it we get to here
            If ws.Range("B" & lrow).Value <> "" Then
                'Write the status we got from the line we deleted into column A.
                ws.Range("A" & lrow).Value = szStatus
            End If
            lrow = lrow + 1
        End If
    Loop

End Sub