LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 88
Default Trying to loop

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
returning back to loop check condition without completing the loop ashish128 Excel Programming 13 April 3rd 08 12:53 PM
Loop to Filter, Name Sheets. If Blank, Exit Loop ryguy7272 Excel Programming 3 February 5th 08 03:41 PM
Naming Worksheets - Loop within a loop issue klysell Excel Programming 0 March 27th 07 11:17 PM
(Complex) Loop within loop to create worksheets klysell Excel Programming 1 March 20th 07 12:03 AM
Advancing outer Loop Based on criteria of inner loop ExcelMonkey Excel Programming 1 August 15th 05 05:23 PM


All times are GMT +1. The time now is 03:09 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright 2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"