Solved: VBA Using find Excel 2003

[Chris66]

I have a spreadsheet that schedules a part from a supplier. This spreadsheet will contain about 3 - 5K of lines a day and will support about 9 production lines. I have found a way to have excel/VBA start adding the quantities of each work order until it reaches 1,000 or slightly more.

I want to have a userform ask the user to input the workorder number they want to start with. I am hung up on how to get the macro to go find the work order # (from the user) in colum "B". My user form has a text box which I call SR. Can someone please help me? I have come so far on this project but am very inexperience with VBA. I am pulling my hair out.

For example, I would want the user form to promt the user to enter the starting release, say 5A321-15...Excel would find Release 5A321-15 go to column "C" (the quantity column) and start adding the quantities until it reached 1,000 or so. And then hightlight the range that were included in the calculation. so if 5A321-15 was in B15 excel would add up C15...C23 and highlight A15 though E15. I hope I am clear in what I am seeking.

[Zack Barresse]

Hi Chris,

Set a variable, something like rngFind and use like so ...

[vba]With Sheets("sheet name")
Set rngFind = .Range("B:B").Find(Me.SR.Value, lookat:=xlWhole, matchcase:=True)
If rngFind Is Nothing Then
Msgbox "Not Found"
Else
Msgbox "Found at " & rngFind.Addresss
.Range("A" & rngfind.Row & ":E" & rngfind.Row).Interior.Colorindex = 3 'or whatever color..
Msgbox Application.Worksheetfunction.Sum(.Range("C" & rngfind.row & ":C" & rngfind.Row + 8))
End If
End With[/vba]

This assumes that SR is a text box that houses the text you want to find in column B. Using an inputbox would be a little different, just adding a couple of extra lines. Although I'm not sure why you would want to use an inputbox when you have a userform right there. Also not sure how you would determine how many rows to go down from the found cell; I assumed 8 from your example..

[Chris66]

Thanks, that helped out a great deal.

[geekgirlau]

Hi Chris,

Welcome to the Board! I've marked this thread as Solved for you - normally you can do this via the "Thread Tools" option at the top of the screen, but it's not working at the moment.