连接取决于上面行中的值

I have an excel chart with a bunch of data

我有一个带有大量数据的excel图表

Every few rows is blank

每隔几行都是空白的

When there is a blank row I would like to concatenate the cells in column A and last 4 characters of column B from the row below, as long as the cell in column A below does not equal "."

当有一个空白行时,我想连接A列中的单元格和下面一行中B列的最后4个字符,只要下面A列中的单元格不等于“。”。

I have the following:

我有以下内容:

Sub Macro3()
'
' Macro3 Macro
'

'
    For Each cell In Columns("A")
        If ActiveCell.Value = "" Then ActiveCell.FormulaR1C1 = _
        "=IF(R[1]C<>""."",CONCATENATE(R[1]C,RIGHT(R[1]C[1],4)),"""")"
    Range("A2").Select

    Next cell

End Sub

1 个解决方案

#1

Why entire Col A?

为什么整个Col A?

If you are using the cell object to loop then why use ActiveCell?

如果您使用单元格对象循环,那么为什么要使用ActiveCell?

My recommendation is to find the last row in Col A and then take that into account in identifying your actual range and then loop through that.

我的建议是找到Col A中的最后一行,然后在确定实际范围时将其考虑在内,然后循环显示。

Is this what you are trying?

这是你在尝试什么?

Sub Sample()
    Dim aCell As Range
    Dim lRow As Range
    Dim ws As Worksheet

    '~~> Change this to the relevant sheet name
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Last row in col A which has data
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Check each cell in the relevant range
        For Each aCell In .Range("A1:A" & lRow)
            If aCell.Value = "" Then _
            aCell.FormulaR1C1 = "=IF(R[1]C<>""."",CONCATENATE(R[1]C,RIGHT(R[1]C[1],4)),"""")"
        Next aCell
    End With
End Sub

#1

Why entire Col A?