英文:
How to xLookup values in different columns to check which one is duplicated
问题
I am trying to do a xLookup on emails in different columns to check which one is duplicated.
The idea is to lookup an email in one column.
If it doesn't exist then move on to lookup in another column.
For example,
if the email is found in A:A then return the said value,
if not found then lookup in B:B and so on.
=XLOOKUP(D2,A:A,A:A,XLOOKUP(D2,B:B,B:B,XLOOKUP(D2,C:C,C:C,"",0,1)))
I am looking for ways to loop it for each column.
I tried alternatives such as Match function but failed to loop it.
英文:
I am trying to do a xLookup on emails in different columns to check which one is duplicated.
The idea is to lookup an email in one column.
If it doesn't exist then move on to lookup in another column.
For example,
if the email is found in A:A then return the said value,
if not found then lookup in B:B and so on.
=XLOOKUP(D2,A:A,A:A,XLOOKUP(D2,B:B,B:B,XLOOKUP(D2,C:C,C:C,"",0,1)))
I am looking for ways to loop it for each column.
I tried alternatives such as Match function but failed to loop it.
答案1
得分: 1
以下是代码的翻译部分:
Sub FindAThing()
Dim emailAddress As String: emailAddress = "joe.bloggs@email.com"
Dim rg As Range: Set rg = ThisWorkbook.Sheets(1).Range("B:M")
On Error Resume Next
Dim lr As String
lr = rg.Cells.Find(What:=emailAddress _
, Lookat:=xlWhole _
, LookIn:=xlValues _
, searchorder:=xlByColumns _
, searchdirection:=xlNext).Address
If Err.Number > 0 Then
MsgBox "无法找到电子邮件地址"
Else
MsgBox "在单元格中找到电子邮件:" & lr
End If
On Error GoTo 0
End Sub
英文:
In response to your comment - perhaps something similar to this
Sub FindAThing()
Dim emailAddress As String: emailAddress = "joe.bloggs@email.com"
Dim rg As Range: Set rg = ThisWorkbook.Sheets(1).Range("B:M")
On Error Resume Next
Dim lr As String
lr = rg.Cells.Find(What:=emailAddress _
, Lookat:=xlWhole _
, LookIn:=xlValues _
, searchorder:=xlByColumns _
, searchdirection:=xlNext).Address
If Err.Number > 0 Then
MsgBox "unable to find email address"
Else
MsgBox "Found email in cell " & lr
End If
On Error GoTo 0
End Sub
答案2
得分: 0
你可以编写一个用户自定义函数(UDF)。
在工作表上使用=FindEmail(D2,$A$5:$D$26)
。
Public Function FindEmail(EmailAddress As String, SearchRange As Range) As Variant
'https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.Volatile
'一个挥发函数必须在工作表上的任何单元格发生计算时重新计算。
'一个非挥发函数仅在输入变量更改时重新计算。
Application.Volatile False
'https://learn.microsoft.com/en-us/office/vba/api/excel.range.find
Dim rFind As Range
With SearchRange
Set rFind = .Find(EmailAddress, .Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext, False)
If Not rFind Is Nothing Then
FindEmail = rFind
'或者FindEmail = rFind.Address返回地址。
'或者FindEmail = rfind.Row返回行。
'或者FindEmail = rfind.Offset(,1)返回下一列。
'或者FindEmail = rFind.Font.Name返回正在使用的字体名称。
Else
'如果未找到值,则返回#N/A - 函数必须返回Variant类型。
FindEmail = CVErr(xlErrNA)
End If
End With
End Function
英文:
You could write a UDF.
Use on a worksheet as =FindEmail(D2,$A$5:$D$26)
Public Function FindEmail(EmailAddress As String, SearchRange As Range) As Variant
'https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.Volatile
'A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet.
'A nonvolatile function is recalculated only when the input variables change.
Application.Volatile False
'https://learn.microsoft.com/en-us/office/vba/api/excel.range.find
Dim rFind As Range
With SearchRange
Set rFind = .Find(EmailAddress, .Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext, False)
If Not rFind Is Nothing Then
FindEmail = rFind
'or FindEmail = rFind.Address to return the address.
'or FindEmail = rfind.Row to return the row.
'or FindEmail = rfind.Offset(,1) to return the next column across.
'or FindEmail = rFind.Font.Name to return the name of the font being used.
Else
'Return #N/A if value not found - function must return Variant for this.
FindEmail = CVErr(xlErrNA)
End If
End With
End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论