本帖最后由 0126 于 2017-5-26 17:39 编辑
Sub b()
On Error Resume Next '程序出错不提醒继续执行下一句
Dim a, x, z, y, sh, ay, at, yq, tc '所有变量声明
sh = ActiveSheet.Cells(1, 250).End(xlToLeft).Column '查找当前第一列有数据的最后一个单元格在第几列
For a = 1 To sh '循环第一列单元格
If InStr(Cells(1, a), "要求数量") Then '判断单元格Cells(1, a)是否有 要求数量 标题的单元格,有查询到就赋值并结束循环
ay = a
Exit For
End If
Next
For a = 1 To sh '下同
If InStr(Cells(1, a), "填充") Then
at = a
Exit For
End If
Next
yq = ActiveSheet.Cells(16666, ay).End(xlUp).Row '要求数量列有数据的最后一个单元格是哪行
tc = ActiveSheet.Cells(16666, at).End(xlUp).Row '填充列有数据的最后一个单元格是哪行
For x = 2 To yq '第几行要求数量
For z = 1 To Cells(x, ay) '确定填充的次数
For y = 2 To tc '对填充行全部执行对比
If Cells(y, at - 1) = Cells(x, ay - 1) And (InStr(Cells(y, at), "ok") Or InStr(Cells(y, at), "OK")) Then '确定名称对应 并且 填充列有大写OK或者有小写ok
Cells(y, at) = "yes"
Exit For
End If
Next
Next
Next
End Sub