Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I'm trying to create a "Do Until" and I'm getting lost do to the lack of
experience. I have a code that works well if I set the active row for each item. To make it easier on the end user I would like it to "loop" until there is a blank row. The list will always start at row 23. The code I have is: Private Sub CommandWeek_Click() Dim intStart As Integer Dim lngEnd As Long Dim wksOrder As Worksheet Dim wksCut As Worksheet Dim rngCut As Range Dim rngFind As Range Dim rngFound As Range 'create worksheet objects Set wksOrder = Sheets("Order") Set wksCut = Sheets("Cut List") 'initialize the starting row intStart = 2 'find the last row on wksCut 'As a side note, you can use .Cells(Rows.Count, "B") lngEnd = wksCut.Cells(Rows.Count, 2).End(xlUp).Row Debug.Print "lngEnd: "; lngEnd 'create the range in column B from the intStart row to the end Set rngCut = wksCut.Range("B" & intStart, "B" & lngEnd) 'column B is the "File" column - do you want to search for this? Set rngFind = wksOrder.Range("B" & ActiveCell.Row) Debug.Print rngFind.Address(External:=True) 'search rngCut for rngFind Set rngFound = rngCut.Find(rngFind.Value, LookIn:=xlValues) Debug.Print rngFind.Address(External:=True) If rngFound Is Nothing Then MsgBox "Weekly Order has been submitted" Else wksCut.Range("F" & rngFound.Row).Value = wksOrder.Cells(rngFind.Row, "C").Value wksCut.Range("E" & rngFound.Row).Value = Date wksCut.Range("G" & rngFound.Row).Value = wksOrder.Range("I21").Value wksCut.Range("H" & rngFound.Row).Value = "Week" End If End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming |