Snasui Excel Forum

Code: Select all

Private Sub NameMTD_Change()


Set sh = ThisWorkbook.Sheets("DEP")




Dim N As Long, lastrow As Long
lastrow = Sheets("DEP").Range("A" & Rows.Count).End(xlUp).Row
For N = 2 To lastrow
If Sheets("DEP").Cells(N, "A").Value = (Me.NameMTD) Or Sheets("DEP").Cells(N, "A").Value = Val(Me.NameMTD) Then
Me.listmtd2 = Sheets("DEP").Cells(N, "B").Value
Me.listmtd3 = Sheets("DEP").Cells(N, "C").Value
Me.listmtd4 = Sheets("DEP").Cells(N, "D").Value
Me.listmtd5 = Sheets("DEP").Cells(N, "E").Value
Me.listmtd6 = Sheets("DEP").Cells(N, "F").Value
Me.listmtd7 = Sheets("DEP").Cells(N, "G").Value
Me.listmtd8 = Sheets("DEP").Cells(N, "H").Value
Me.listmtd9 = Sheets("DEP").Cells(N, "I").Value
Me.listmtd10 = Sheets("DEP").Cells(N, "J").Value
Me.listmtd11 = Sheets("DEP").Cells(N, "K").Value
Me.listmtd12 = Sheets("DEP").Cells(N, "M").Value
Me.listmtd13 = Sheets("DEP").Cells(N, "P").Value
Me.listmtd14 = Sheets("DEP").Cells(N, "Q").Value
Me.listmtd15 = Sheets("DEP").Cells(N, "R").Value
Me.listmtd16 = Sheets("DEP").Cells(N, "S").Value


Me.COMMTD1 = Sheets("DEP").Cells(N, "V").Value
Me.COMMTD2 = Sheets("DEP").Cells(N, "X").Value
Me.COMMTD3 = Sheets("DEP").Cells(N, "Z").Value
Me.COMMTD4 = Sheets("DEP").Cells(N, "AB").Value
Me.COMMTD5 = Sheets("DEP").Cells(N, "AD").Value
Me.COMMTD6 = Sheets("DEP").Cells(N, "AF").Value
Me.COMMTD7 = Sheets("DEP").Cells(N, "AM").Value
Me.COMMTD8 = Sheets("DEP").Cells(N, "AS").Value
Me.COMMTD9 = Sheets("DEP").Cells(N, "AW").Value
Me.COMMTD10 = Sheets("DEP").Cells(N, "AY").Value
Me.COMMTD11 = Sheets("DEP").Cells(N, "BA").Value

Me.YESMTD1 = Sheets("Dep").Cells(N, "U").Value
Me.YESMTD2 = Sheets("Dep").Cells(N, "W").Value
Me.YESMTD3 = Sheets("Dep").Cells(N, "Y").Value
Me.YESMTD4 = Sheets("Dep").Cells(N, "AA").Value
Me.YESMTD5 = Sheets("Dep").Cells(N, "AC").Value
Me.YESMTD6 = Sheets("Dep").Cells(N, "AE").Value
Me.YESMTD7 = Sheets("Dep").Cells(N, "AL").Value
Me.YESMTD8 = Sheets("Dep").Cells(N, "AO").Value
Me.YESMTD9 = Sheets("Dep").Cells(N, "AU").Value
Me.YESMTD10 = Sheets("Dep").Cells(N, "AX").Value
Me.YESMTD11 = Sheets("Dep").Cells(N, "AZ").Value
















End If
Next N






   End Sub

Code: Select all

Private Sub NextMTD_Click()

Set sh = ThisWorkbook.Sheets("DEP")
nextRow = ActiveCell.Row + 1
Cells(nextRow, 1).Activate
NameMTD.Value = Cells(nextRow, 1)



End Sub