INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel VB 2010

Excel VB 2010

(OP)
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

RE: Excel VB 2010

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.

RE: Excel VB 2010

(OP)
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

RE: Excel VB 2010

>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

RE: Excel VB 2010

(OP)
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

RE: Excel VB 2010

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.

RE: Excel VB 2010

(OP)
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"

RE: Excel VB 2010

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

Change

CODE

'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 

to

CODE

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

Or if we want to reduce the code even further:

CODE

' Copy row from current sheet into Sheet2
Rows(LSearchRow).Copy Sheets("Sheet2").Rows(LCopyToRow) 

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

CODE

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 

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.

RE: Excel VB 2010

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.

RE: Excel VB 2010

(OP)
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


RE: Excel VB 2010

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: VBA Visual Basic for Applications (Microsoft) is a better place to explore Excel VBA issues.

RE: Excel VB 2010

(OP)
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.

RE: Excel VB 2010

>. 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close