Excel VBA 循环遍历 2 列，只复制符合条件的一个范围的单元格

Countif 的范围不正确。

Sub LoopForCondFormatCells()

    Dim sht3 As Worksheet, sht4 As Worksheet
    Dim ColB As Range, ColG As Range, ColBG As Range, c As Range
    Dim Wf As WorksheetFunction
    Dim vR() As Variant
    Dim k As Long, j As Integer

    Set Wf = WorksheetFunction

    Set sht3 = Sheets("Compare")
    Set sht4 = Sheets("Print ready")

    Set ColG = sht3.Range("B3:B88")
    Set ColB = sht3.Range("G3:G86")
    'ColBG1 = ColB & "," ColG
    'Set ColBG = Union(ColG, ColB)
'In the following For Each strings, I would like it to look in the range ColBG, _
but it should only return the value it finds in ColB... _
But I don't know how to write the code to do so
For Each c In ColB.Cells
    If Not IsEmpty(c) Then
        With Wf
            n = .CountIfs(ColG, c) 'Error here
            If n = 0 Then
                k = k + 1
                ReDim Preserve vR(1 To 3, 1 To k)
                For j = 1 To 3
                    vR(j, k) = c.Offset(0, j - 2)
                Next j
            End If
        End With
    End If
Next
sht4.Range("a1").Resize(k, 3) = Wf.Transpose(vR) '<~~The unique values  are written below cell a1 in Sheet 4.
End Sub

上面使用数组更快，下面是如何复制范围。

Sub LoopForCondFormatCells()

    Dim sht3 As Worksheet, sht4 As Worksheet
    Dim ColB As Range, ColG As Range, ColBG As Range, c As Range
    Dim Wf As WorksheetFunction
    Dim vR() As Variant
    Dim k As Long, j As Integer
    Dim HosKvikOffIns As Range '<~~Declare a variable

    Set Wf = WorksheetFunction

    Set sht3 = Sheets("Compare")
    Set sht4 = Sheets("Print ready")

    Set ColG = sht3.Range("B3:B88")
    Set ColB = sht3.Range("G3:G86")

    Set HosKvikOffIns = sht4.Range("a1") '<~~ First, set the varialble
For Each c In ColB.Cells
    If Not IsEmpty(c) Then
        With Wf
            n = .CountIfs(ColG, c) 'Error here
            If n = 0 Then
                c.Offset(0, -1).Resize(1, 3).Copy HosKvikOffIns
                Set HosKvikOffIns = HosKvikOffIns.Offset(1, 0)
            End If
        End With
    End If
Next

End Sub