检查合并的单元格并进行比较以从比较的单元格值中设置唯一值 [英] Check merged cell and compare adjacent to set unique value from compared cells values

问题描述

我正在Excel 2010中编写一个宏，用于解决以下问题：

我有两列，一列具有Key字符串值，一列具有uuid。这个想法是，每个键都应该只有一个uuid，但是就像现在的表一样，键单元格可以是合并单元格或单个单元格。
宏需要识别合并的单元格和不合并的单元格，因此，我有两个选择：

I'm writing a macro in Excel 2010 for a problem that is as follows:
I have two columns, one with a Key string value and one with a uuid. The idea is that every key should have only one uuid but as the table is now, key cell could be merged cells or single cells. The macro needs to recognize which cells are merged and which are not, so, I have two options:

• 如果单元格是合并后，检查其所有相邻单元格，选择第一个uuid值并将其复制/粘贴到其他相邻单元格，即下面的单元格（可能带有 Offset（） ）


• 如果未合并单元格，但键值在多个单元格中重复，则将uuid值复制/粘贴到相邻的单元格中。

基本上是检查合并的单元格 MergeArea ，但是我不知道是否需要遍历其地址或检查范围内的单元格偏移量为 Offset（0,1）或其他值。
使用我的代码，我可以知道单元格是否已合并，但是现在，我如何遍历相邻单元格的值呢？

So basically is to check merged cells MergeArea but I don't know if I need to iterate through its addresses or check cells in the range with an offset of Offset(0,1) or what. With my code I can know if the cells are merged but now, how con I iterate through it's adjacent cells values?

现在的代码：

Code as is now:

Sub CopyUUID()
Dim lRow As Long
Dim rng As Range
Dim ws As Worksheet
Dim rMerged As Range
Dim value As Variant

Set ws = Sheets(ActiveSheet.Name)

On Error GoTo ExitProgram 'If an error happens within the execution, skips it and continue in next step
Application.DisplayAlerts = False 'We can cancel the procedure without errors

With ws
    lRow = .Range("F" & .Rows.count).End(xlUp).row
    Set rng = .Range(.Cells(3, 6), .Cells(lRow, 6))
    rng.Select
    For Each cell In rng
        If cell.MergeCells Then
            'Code for merged cells
        Else
            'Code to use for single cells
        End If
    Next cell
End With
ExitProgram:
   Exit Sub
End Sub

推荐答案

尝试以下代码。请注意，这将覆盖UUID的当前内容，因此请在测试之前制作备份副本。如果您不想修改UUID列，则可以对其进行修改以满足您的需求。

Try the following code. Note that this is going to overwrite the current contents of UUID, so make a backup copy before testing. If you don't want the UUID column modified, you can modify this to suit your needs.

Sub CopyUUID()
    Dim lRow As Long
    Dim rng As Range
    Dim c As Range
    Dim ws As Worksheet
    Dim rMerged As Range
    Dim value As Variant

    Set ws = Sheets(ActiveSheet.Name)

    On Error GoTo ExitProgram 'If an error happens within the execution, skips it and continue in next step
    ' Application.DisplayAlerts = False 'We can cancel the procedure without errors

    With ws
        lRow = .Range("F" & .Rows.Count).End(xlUp).Row
        Set rng = .Range(.Cells(3, 6), .Cells(lRow, 6))
        ' rng.Select
        For Each c In rng

            If c.MergeCells Then
                'Code for merged cells
                c.Offset(0, 1).Formula = c.MergeArea.Cells(1, 1).Offset(0, 1).Formula
            Else
                'Code to use for single cells
                If c.Formula = c.Offset(-1, 0).Formula Then
                    c.Offset(0, 1).Formula = c.Offset(-1, 1).Formula
                End If
            End If
        Next c
    End With
    ExitProgram:
       Exit Sub
End Sub

在MergedCell中时，它使UUID与合并区域中第一个单元格的UUID相同。当不在MergedCell中时，如果Key与上面的行相同，它将从上面的行复制UUID。

When in a MergedCell, it makes the UUID the same as the UUID of the first cell in the merged area. When not in a MergedCell, it copies UUID from the row above if Key is the same as the row above.

我更改了变量 cell 到 c （我不喜欢使用可以与内置函数混淆的变量名）并注释了几行。

I changed your variable cell to c (I don't like to use variable names that can be confused with built-ins) and commented out a couple of lines.

希望这会有所帮助

这篇关于检查合并的单元格并进行比较以从比较的单元格值中设置唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！