比较两个单元格范围,并根据比较返回一个值 [英] Compare two ranges of cells and return a value based on comparison
问题描述
示例:
比较 Sheet1 A1 B1 C1 D1
to Sheet 2 A1 B1 C1 D1
并返回 Sheet 2 E1
value
在我的头上,它就像一个多列 VLOOKUP
。
如果您可以将作业代码移动到E2,那么从链接siddharth发布的更改将会起作用:
Dim a As Application
设置a =应用程序
如果加入(a.Transpose(a.Transpose(Sheets(1).Rows 1).Value)),chr(0))= _
加入(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
设置a =应用程序
Dim rngA As Range
Dim rngB As Range
设置rngA =范围(A1,D1)
设置rngB =范围(A2,D2)
如果加入(a.Transpose(a.Transpose rngA.Value)),chr(0))= _
加入(a.Transpose(a.Transpose(rngB.Value)),chr(0))然后
MsgBox(Sheets(2).Cells(2,E))
End If
只要比较的范围只有1行。如果任一范围跨越一行,它将无法工作。
Edit2
Sub getValuesOnRow()
Dim sourceRange作为范围
Dim targetRange As Range
设置sourceRange = ActiveSheet.Range(Cells(1,1),Cells(5,1))
设置targetRange = ActiveSheet.Cells 7,1)
sourceRange.Copy
targetRange.PasteSpecial粘贴:= xlPasteValues,操作:= xlNone,SkipBlanks:= False,转置:= True
结束Sub
Sub Compare()
Dim a As Application
Set a = Application
Dim rngA As Range
Dim rngB As Range
设置rngA =表(1).Range(A1,A6)
设置rngB =表(2).Range(A1,A6)
如果加入(a.Transpose(a.Transpose(Sheets(1).Rows(7).Value)),chr(0))= _
加入(a.Transpose (a.Transpose(Sheets(2).Rows(7).Value)),chr(0))然后
表格(1).Cells(6,A) .Value = Sheets(2).Cells(6,A)
End If
End Sub
所以有2种方法。第一个,在一个特定的列,在示例中,列1和前5个单元格,并将其放在1行。在示例中,第7行。然后,转置方法,将行进行比较。所以可以使用第一种方法来获取一行上的特定列,如果比较为真,则可以使用第二种方法比较2行并返回特定的单元格。
Can anyone point me at a function or code that can compare a range of cells to a range of cells in another sheet and return a value. The lists have the same format:
Example:
Compare Sheet1 A1 B1 C1 D1
to Sheet 2 A1 B1 C1 D1
and return Sheet 2 E1
value
In my head it's like a multiple column VLOOKUP
.
If you can move the job code to E2 then this, adapted from the link siddharth posted will work:
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
Change the rows as necessary, and instead of msgbox, save it as a variable or however you want.
Edit
the following will work also:
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
provided that the ranges to compare are only 1 row each. if either range spans more than one row it wont work.
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
So that there is 2 methods. the first, takes a particular column, in the example, column 1, and the first 5 cells, and puts it on 1 row. in the example, row 7. Then the transpose method, takes the row and compares it. so the first method can be used to get a particular column on a row, and the second can be used to compare 2 rows and return a particular cell if the comparison is true.
这篇关于比较两个单元格范围,并根据比较返回一个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!