PDA

View Full Version : Solved: Selecting a range based on text box entry



Chris66
04-26-2006, 01:44 PM
I have a spread sheet that I have been working on for sometime. I am really hung up on this part:
I have a form with two text boxes, it asks the user to enter the starting point which will be in "B", and an ending point which will also be in "B". Once excel finds the starting point, I need rows A,B,C,D of whatever row, along with everything in the ending point, A,B,C,D in whatever row is the ending point, with everything in between, Then I need to copy this range to J1:M?. Can someone please help me?

Jacob Hilderbrand
04-26-2006, 02:07 PM
Are the entering the row numbers to start and end, or are they entering text that we need to search for?

Once we know the rows we can do this:

Assuming the rows are stored to variables 'x' and 'y'.

Range("A" & x & ":D" & y).Copy Destination:=Range("J1")

Chris66
04-26-2006, 02:39 PM
I must be doing something wrong here, I am getting Method 'Range' of object failed. I have my Starting point called "srcop" and myending point called "ercop".

With Sheets("sl data")
Set rngfind = .Range("B:B").Find(srcop, , lookat:=xlWhole, MatchCase:=True)
Set rngfind2 = .Range("B:B").Find(ercop, , lookat:=xlWhole, MatchCase:=True)
If rngfind Is Nothing Then
MsgBox "Not Found", vbOKOnly
End

End If
End With

I pasted your code
Range("A" & srcop & "d" & ercop).Copy Destination:=J1

Jacob Hilderbrand
04-26-2006, 03:10 PM
srcop and ercop are Strings right? rngfind and rngfind2 are Ranges. So we can use the Row property of the range.


Range("A" & rngfind.Row & ":D" & rngfind2.Row).Copy Destination:=Range("J1")


Also you need to check if rngfind2 is Nothing as well.

Chris66
04-27-2006, 08:16 AM
Thank you so much that worked perfectly