PDA

View Full Version : Sleeper: Cant get this code to work



austenr
09-20-2005, 08:06 AM
I am trying to execute this code to execute. Keeps erroring out in the paste function. Any ideas would be appreciated.


Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 2
LSearchRow = 2
'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column E = "Mail Box", copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = "Mail Box" Then
'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
On Error Resume Next
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select
End If
LSearchRow = LSearchRow + 1
Wend
'Position on cell A3
Application.CutCopyMode = False
Range("A1").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub

Zack Barresse
09-20-2005, 08:47 AM
Hmm, well without much testing (and boiling out your comments, for board readability), maybe ...


Sub SearchForString()
Dim LSearchRow As Long 'Integer
Dim LCopyToRow As Long 'Integer
On Error Goto Err_Execute
LSearchRow = 2
LCopyToRow = 2
While Len(Range("A" & LSearchRow).Value) > 0
If Range("E" & LSearchRow).Value = "Mail Box" Then
Rows(LSearchRow).Copy
On Error Resume Next '<<???
Sheets("Sheet2").Rows(LCopyToRow).PasteSpecial(xlPasteAll)
LCopyToRow = LCopyToRow + 1
End If
LSearchRow = LSearchRow + 1
Wend
Application.CutCopyMode = False
Range("A1").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub


Not really sure why you have the second On Error statement in there; you already have the first error handler.

austenr
09-20-2005, 08:52 AM
Thanks Zack. That got it. One other thing, how can you get it to check for the value in any cell on that row?

Zack Barresse
09-20-2005, 02:39 PM
Hmm, not sure what you mean. If you mean the fourth column in that particular row, you can use something like this ...



If Cells(LSearchRow, 4).Value = "whatever" Then
'true
Else
'false
End If

austenr
09-20-2005, 10:10 PM
What I mean is, how do I loop through and find the word in any column on that row?

Zack Barresse
09-21-2005, 02:37 PM
Add a few lines...


'add some varibles..
Dim rngFound as range
'.. add this line in your loop
Set rngFound = Rows(LSearchRow).Find("your word", matchcase:=true)
'Test if range is there or not ..
If not rngFound is nothing then msgbox "Was found at " & rngFound.address