用VBA实现下面的业务:1. 对M变量列,从MN(N为行变量)单元格开始,到最大的行数的单元格区间的每一个单元格,按字符变量c分割单元格内的字符串,形成字符数组arri, 2. 遍历数组arri,若数组元素的值在名为xxx工作簿的A列里,则设置该值为N列对应行的值,
时间: 2023-05-24 19:05:12 浏览: 60
Sub SeparateAndCheck()
Dim c As String '分割用的字符变量c
c = ","
Dim lastRow As Long '最大行数
lastRow = Cells(Rows.Count, "M").End(xlUp).Row
Dim i As Long '循环变量
For i = 13 To lastRow '从第13行开始循环,因为MN位置是第13行
Dim cellValue As String
cellValue = Cells(i, "M").Value '取出M列每个单元格的值
Dim arr() As String
arr = Split(cellValue, c) '按c字符分割值,生成数组
Dim j As Long '循环变量
For j = LBound(arr) To UBound(arr) '遍历数组,从第一个元素到最后一个元素
Dim checkValue As String
checkValue = Trim(arr(j)) '去掉元素值两端的空格
Dim wb As Workbook
Set wb = Workbooks("xxx工作簿.xlsx") '打开名为xxx工作簿的工作簿
Dim ws As Worksheet
Set ws = wb.Worksheets("Sheet1") '打开该工作簿的名为Sheet1的工作表
Dim findRng As Range
Set findRng = ws.Range("A:A").Find(What:=checkValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) '在A列查找checkValue
If Not findRng Is Nothing Then '如果找到了checkValue
Dim rowNum As Long
rowNum = findRng.Row '获取该值所在的行数
Cells(i, "N").Value = rowNum '将该值所在行的值赋给M列对应行的N列
End If
Next j
Next i
End Sub