如何检查两个范围值是否相等 [英] How to check if two ranges value is equal

问题描述

如果整行中的值相同，我想合并列中的单元格。

例如。如果A1：G1的范围与A2：G2相同，则我希望合并A1：A2单元格，B1：B2到G1：G2。

使用下面的代码，我会遇到运行时错误13：类型不匹配。我假设问题在于检查两个范围的相等性。

I want to merge cells in columns, if there is the same value in whole row.
Eg. If A1:G1 range is the same as A2:G2 I want to merge A1:A2 cells, B1:B2 to G1:G2.
With my code below I get run time error 13: type mismatch. I'm assuming, that problem is with checking equality of two ranges.

Dim i As Long, j As Long, row as Long
row = Cells(Rows.Count, 6).End(xlUp).row
For i = row To 7 Step -1
        If Range(Cells(i, 7), Cells(i, 24)).Value = Range(Cells(i - 1, 7), Cells(i - 1, 24)).Value Then
        For j = 7 To 24 Step 1
            Range(Cells(i, j), Cells(i - 1, j)).Merge
        Next j
        End If
Next i

问题是，如何检查两个范围值是否相等？

The question is, how to check if both ranges values are equal?

在注释后编辑：
使用下面的代码，它实际上有效

Edit after comments: With the code below it actually works

Dim i As Long, j As Long, row As Long
row = Cells(Rows.Count, 6).End(xlUp).row
For i = row To 7 Step -1
        If Join(Application.Transpose(Application.Transpose(Range(Cells(i, 7), Cells(i, 24)))), Chr(0)) = Join(Application.Transpose(Application.Transpose(Range(Cells(i - 1, 7), Cells(i - 1, 24)))), Chr(0)) Then
        For j = 7 To 24 Step 1
            Range(Cells(i, j), Cells(i - 1, j)).Merge
            Application.DisplayAlerts = False
        Next j
        End If
Next i

但是，我想知道为什么您（@Pᴇʜ）将函数分隔为第一行和最后一行。

However, I'm wondering, why you(@Pᴇʜ) separeted the function for first and last rows.

同样，使用我的代码，在不合并单元格的情况下，我可以使用te循环更改单元格颜色：

Also, with my code, without merging cells I had te loop for change cell color:

Dim row As Long
row = Cells(Rows.Count, 6).End(xlUp).ro
Do Until IsEmpty(Cells(row, 3))
     If row Mod 2 <> 0 Then
       Range(Cells(row, 3), Cells(row, 24)).Interior.Color = RGB(217, 225, 242)
     Else
       Range(Cells(row, 3), Cells(row, 24)).Interior.Color = xlNone
     End If
     row = row + 1
Loop

单元格合并后如何处理？

How to deal with that after cells are merged?

推荐答案

问题是…

Range(Cells(i, 7), Cells(i, 24)).Value

返回值数组，但是不能将值数组与 = 。因此，您需要遍历所有这些值，并将每个值与

returns an array of values, but you cannot compare an array of values with =. Therefore you need to loop throug all these values and compare each value with the corresponding value in

Range(Cells(i - 1, 7), Cells(i - 1, 24)).Value

因为您已经有了此循环将您的 If 语句移至循环中：

Since you already have this loop just move your If statement to check this into the loop:

Dim iRow As Long, iCol As Long, LastRow as Long
LastRow = Cells(Rows.Count, 6).End(xlUp).row
For iRow = LastRow To 7 Step -1
    For iCol = 7 To 24 Step 1
        If Cells(iRow, iCol).Value = Cells(iRow - 1, iCol).Value Then
            Range(Cells(iRow, iCol), Cells(iRow - 1, iCol)).Merge
        End If
    Next iCol 
Next iRow 

<请注意，我将变量命名更改为更有意义的名称。这也避免了使用 Row 作为变量名称，这是Excel本身惯用的变量。

Note that I changed the variable naming to more meaningful names. This also avoids using Row as variable name which is alerady used by Excel itself.

根据评论编辑

Option Explicit

Sub Test()
    Dim RangeToMerge As Range
    Set RangeToMerge = Range("C5:F14")

    Dim FirstMergeRow As Long
    FirstMergeRow = 1

    Dim iRow As Long, iCol As Long
    For iRow = 1 To RangeToMerge.Rows.Count - 1
        If Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(RangeToMerge.Rows(FirstMergeRow).Value)), "|") <> _
           Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(RangeToMerge.Rows(iRow + 1).Value)), "|") Then
            If iRow <> FirstMergeRow Then
                For iCol = 1 To RangeToMerge.Columns.Count
                    Application.DisplayAlerts = False
                    RangeToMerge.Cells(FirstMergeRow, iCol).Resize(rowsize:=iRow - FirstMergeRow + 1).Merge
                    Application.DisplayAlerts = True
                Next iCol
            End If
            FirstMergeRow = iRow + 1
        End If
    Next iRow

    'merge last ones
    If iRow <> FirstMergeRow Then
        For iCol = 1 To RangeToMerge.Columns.Count
            Application.DisplayAlerts = False
            RangeToMerge.Cells(FirstMergeRow, iCol).Resize(rowsize:=iRow - FirstMergeRow + 1).Merge
            Application.DisplayAlerts = True
        Next iCol
    End If
End Sub

将打开以下内容

进入

这篇关于如何检查两个范围值是否相等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！