如何用excel表格实现多个条件判断,返还指定值
2个回答
展开全部
粘贴如下代码到该表格的VBA中,运行就得到你想要的结果!!!!!!!
Sub sums()
Set regx = CreateObject("vbscript.regexp")
regx.Global = True
i = Cells(Rows.Count, 1).End(xlUp).Row
arr = Range("a2:a" & i)
arr1 = Range("b2:c9")
For j = 1 To UBound(arr1)
For k = 1 To UBound(arr)
regx.Pattern = arr1(j, 1)
If regx.test(arr(k, 1)) = True Then
'将A列对应结果放在D列
Cells(k + 1, 4) = arr1(j, 2)
End If
Next
Next
End Sub
Sub sums()
Set regx = CreateObject("vbscript.regexp")
regx.Global = True
i = Cells(Rows.Count, 1).End(xlUp).Row
arr = Range("a2:a" & i)
arr1 = Range("b2:c9")
For j = 1 To UBound(arr1)
For k = 1 To UBound(arr)
regx.Pattern = arr1(j, 1)
If regx.test(arr(k, 1)) = True Then
'将A列对应结果放在D列
Cells(k + 1, 4) = arr1(j, 2)
End If
Next
Next
End Sub
追问
感谢您的帮助,请教您假设B列和C列的条件与返还值不止这8个,可能有30个条件和返还值,且对应的结果页不放在D列,放在任意列,请问您我该怎么修改这段代码,谢谢
追答
只需简单几处修改就行了!
Sub sums()
Set regx = CreateObject("vbscript.regexp")
regx.Global = True
i = Cells(Rows.Count, 1).End(xlUp).Row
t=Cells(Rows.Count, "C").End(xlUp).Row
arr = Range("a2:a" & i)
arr1 = Range("b2:c" & t)
For j = 1 To UBound(arr1)
For k = 1 To UBound(arr)
regx.Pattern = arr1(j, 1)
If regx.test(arr(k, 1)) = True Then
'将A列对应结果放在D列,(如果放在哪列就将下面的D改成哪列,如F,)
Cells(k + 1, "D") = arr1(j, 2)
End If
Next
Next
End Sub
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询
广告 您可能关注的内容 |