excel - Excel - 如何循環遍歷一行數據,從該行中的特定單元格中提取值,在另一行中查找,然後在交叉框中輸入值

51 1


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



时间: 原作者:

99 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: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



原作者:
...