Excel VB 2010

[Cpreston]

Hi

I am trying to run a macro (found on google)in excel 2010.

I have stepped into the code and it gets to point of going to sheet 2 (cell A2) and then jumps directly to the msgbox 'An Error Occurred'.
It finds and selects the first row, goes to sheet 2 and then the error occurred. I cannot see why this is happening (slight novice with code), any ideas please. (code as it is now below), Thanks

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

'Start search in row 4
LSearchRow = 4

'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 H = LSearchValue, copy entire row to Sheet2
If Range("H" & CStr(LSearchRow)).Value = LSearchValue 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
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("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

 

[strongm]

Excel VB is VBA. You'll probably be better served by asking in forum707

 

[Jasen]

Your usage of "Rows()" is incorrect.
It expects an integer, not a string with a colon in it. I'm really not even sure what you're trying to do with that line, as the input you give will look like "1:1" or "25:25". Depending what you're trying to do there, you might be better off using the Range() method instead, it can take an input like this: Range("A1:B5").Select

I would also suggest you change the line in your error handler from MsgBox "An Error Occurred." to MsgBox Err.Description
This will at least tell the actual error you're getting and make it easier to figure out what's wrong.

 

[Cpreston]

Hi

Thanks for the reply jasen. What should happen is that it searches for the value entered (may appear more than once)
Select them and then copy them into sheet2.

I will try and use the MsgBox Err.Description and see what error it gives and get back.

Thanks

 

[strongm]

>Your usage of "Rows()" is incorrect.

Nope. A string is fine. A string with a colon is fine. And it doesn't need to be an integer

>the input you give will look like "1:1"

Perfectly fine

>you might be better off using the Range()

Rows is a Range

 

[Cpreston]

Hi

I am quite a novice at programming and as I mentioned I got this example off the Internet and tried to adapt it to my needs
I would not know how to sue a range within the programming

Thanks

 

[strongm]

My point is that the code's usage of Rows is fine, contrary to jasen's assertions.

His advice to modify the error handler, however, is sound.

 

[Cpreston]

I applied the error handler and now I get this when I run the code

I am getting Microsoft Excel: "Select method of range class failed

 

[strongm]

Fairly common when using select and changing worksheets. Usual advice is to try and avoid using Select wherever possible.

Change

[blue][green]'Select row in Sheet1 to copy[/green]
 Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
 Selection.Copy

 [green]'Paste row into Sheet2 in next row[/green]
 Sheets("Sheet2").Select
 Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
 ActiveSheet.Paste[/blue]

to

[blue][green]' Copy row from current sheet into Sheet2[/green]
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Copy Sheets("Sheet2").Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow))[/blue]

Or if we want to reduce the code even further:

[blue][green]' Copy row from current sheet into Sheet2[/green]
Rows(LSearchRow).Copy Sheets("Sheet2").Rows(LCopyToRow)[/blue]

And there is more than one way to skin a cat. For example (assuming a fairly uncomplicated source sheet):

[blue]Public Sub SearchForString()
    Application.ScreenUpdating = False
    ActiveSheet.Rows.AutoFilter Field:=8, Criteria1:=InputBox("Please enter a value to search for.", "Enter value")
    ActiveSheet.Rows.Copy Sheets("Sheet2").Rows(2)
    ActiveSheet.Rows.AutoFilter
    MsgBox "All matching data has been copied."
    Application.ScreenUpdating = True
End Sub[/blue]

Note that the error that you have been getting is an Excel issue, rather than VB, which is why it would have been better to post this question in the appropriate forum, as previously advised.

 

[Jasen]

Ah, that makes sense then. I was going by what the Intellisense was reporting as the signature for the rows() object. I keep forgetting how limited the VBA version is compared to VS.

 

[Cpreston]

Hi

Sorry if in wrong post, I have tried to adapt the code to your suggestions but seem to be digging a deeper hole. I am still getting the Select method of range class failed" message (also changed to sheet 3. the code looks like below, any ideas what needs changing please to get it to work, thanks

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

'Start search in row 4
LSearchRow = 4

'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 H = LSearchValue, copy entire row to Sheet2
If Range("H" & CStr(LSearchRow)).Value = LSearchValue Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet3 in next row
Sheets("Sheet3").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

' Copy row from current sheet into Sheet3
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Copy Sheets("Sheet3").Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow))

'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("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox Err.Description

End Sub

 

[SkipVought]

I thought that you stated in another post in another forum that this was solved with a query or some other method. I remember having recoded your example but stopped short from posting by your conclusion.

There's a whole lot better way of getting data from one sheet to another via VBA. and as has been previously suggested, the Activate or Select methods are better avoided AND forum707 is a better place to explore Excel VBA issues.

 

[Cpreston]

Hi

Yes I got intrigued with the coding and was trying to get it working, I have posted in the correct forum, thanks for all the comments.

 

[strongm]

>. I am still getting the Select method of range class failed" message

Because you've left the lines of code in that I suggested should be replaced/changed.