PDA

View Full Version : Solved: Selecting a range using variables?



Simon Lloyd
10-30-2006, 07:09 PM
Hi all i'm trying to write some code to find whatever i put in an inputbox then when found select that row and all the rows above it, i have the code it just i get a runtime 1004 error with the line Rows("i:AR").Copy
heres the rest of the code which works fine up to the line above!

Sub Macro1()
Dim T1
Dim AR As Variant
Dim i
i = 1
T1 = InputBox("Enter text you wish to find", "Text Finder")
Cells.Find(What:=T1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
AR = ActiveCell.Row
For i = 1 To AR
Rows("i:AR").Copy'''''''problem when the code gets to here!
Next
End Sub
Regards,
Simon

geekgirlau
10-30-2006, 07:24 PM
Sub Macro1()
Dim T1
Dim AR As Variant
Dim i
i = 1
T1 = InputBox("Enter text you wish to find", "Text Finder")
Cells.Find(What:=T1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
AR = ActiveCell.Row

Rows("1:" & AR).Copy
End Sub

Simon Lloyd
10-30-2006, 07:27 PM
Thanks Geek ( i think, strange name, would rather have said thanks Julie or Carol etc....but anyway..Thanks!)

I dont know why i didnt try that combination i tried every other!

Regards,
Simon