excel - Excel - 如何循环遍历一行数据,从该行中的特定单元格中提取值,在另一行中查找,然后在交叉框中输入值


Sub Button1_Click()



Dim i As Long


Dim lnCol As Long



For i = 5 To Range("A20")


 If Not IsEmpty(Cells(i, 1).Value) Then


 StartDate = Cells(i, 2).Value



 lnCol = Sheet3.Cells(4, 1).EntireRow.Find(What:=StartDate, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).column



 Cells(i, lnCol).Value ="n"



 End If



Next i



End Sub



时间: 作者:

你可以试试:


Sub Test()



Dim cl As Range


Dim col As Long



With Sheet1 'Change to sheets CodeName you interested in


 For Each cl In .Range("A5:A20").SpecialCells(2, 2)


 col = .Range("4:4").Find(What:=cl.Offset(, 1).Value, LookIn:=xlValues, Lookat:=xlWhole).Column


 .Cells(cl.Row, col).Value ="n"


 Next cl


End With



End Sub



备注:.Range("A5:A20").SpecialCells(2, 2)只有当你确定至少有一个名字,这可防止完全迭代。 但是,当全部为空时将引发错误

我认为你真正想要的是检索最后使用行的动态方法,在这种情况下,请尝试以下操作:


Sub Test()



Dim cl As Range


Dim col As Long



With Sheet1 'Change to sheets CodeName you interested in


 For Each cl In .Range("A5:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)


 col = .Range("4:4").Find(What:=cl.Offset(, 1).Value, LookIn:=xlValues, Lookat:=xlWhole).Column


 .Cells(cl.Row, col).Value ="n"


 Next cl


End With



End Sub



作者:
...