任何人都可以将我指向一个可以将一系列单元格与另一张纸中的一系列单元格进行比较并返回值的函数或代码。列表具有相同的格式:
示例:
将Sheet1 A1 B1 C1 D1
与Sheet 2 A1 B1 C1 D1
进行比较,然后返回Sheet 2 E1
值
在我的脑海中,就像一个多列VLOOKUP
。
如果您可以将作业代码移至e2,则根据链接siddharth发布的适用于此:
Dim a As Application
Set a = Application
If Join(a.Transpose(a.Transpose(Sheets(1).Rows(1).Value)), chr(0)) = _
Join(a.Transpose(a.Transpose(Sheets(2).Rows(1).Value)), chr(0)) Then
MsgBox (Sheets(2).Cells(2, "E"))
End If
根据需要更改行,而不是MSGBox,将其保存为变量或您想要的。
编辑
以下内容也将起作用:
Dim a As Application
Set a = Application
Dim rngA As Range
Dim rngB As Range
Set rngA = Range("A1", "D1")
Set rngB = Range("A2", "D2")
If Join(a.Transpose(a.Transpose(rngA.Value)), chr(0)) = _
Join(a.Transpose(a.Transpose(rngB.Value)), chr(0)) Then
MsgBox (Sheets(2).Cells(2, "E"))
End If
规定要比较的范围每行只有1行。如果两个范围跨越一行,则无法正常工作。
edit2
Sub getValuesOnRow()
Dim sourceRange As Range
Dim targetRange As Range
Set sourceRange = ActiveSheet.Range(Cells(1, 1), Cells(5, 1))
Set targetRange = ActiveSheet.Cells(7, 1)
sourceRange.Copy
targetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
Sub Compare()
Dim a As Application
Set a = Application
Dim rngA As Range
Dim rngB As Range
Set rngA = Sheets(1).Range("A1", "A6")
Set rngB = Sheets(2).Range("A1", "A6")
If Join(a.Transpose(a.Transpose(Sheets(1).Rows(7).Value)), chr(0)) = _
Join(a.Transpose(a.Transpose(Sheets(2).Rows(7).Value)), chr(0)) Then
Sheets(1).Cells(6, "A").Value = Sheets(2).Cells(6, "A")
End If
End Sub
有2种方法。第一个,在示例1列和前5个单元格中获取特定的列,并将其放在1行上。在示例中,第7行。然后,转置方法进行行并进行比较。因此,第一种方法可用于在一行中获取特定的列,并且第二个方法可用于比较2行,并在比较为真时返回特定的单元格。