Results 1 to 9 of 9

Thread: VBA Selection error

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    11

    VBA Selection error

    Hello Experts,

    I am getting the error "Wrong number of arguments or invalid property assignment" on the last two lines.

    Dim oXL As Object
    Dim wbSht As Worksheet
    Dim wbBook As Workbook
    Set oXL = CreateObject("Excel.Application")
    Set wbSht = ActiveWorkbook.Sheets(1)
    Dim iFieldCount As Integer

    wbSht.Range("A1").Select
    wbSht.Range(Selection, Selection.End(xlDown)).Select
    iFieldCount = Selection.Rows.count


    What I'm attempting is to do some editing to an Excel worksheet run from inside a Word form. Everything works up to the point of selecting the range to find the number of rows. I am a "newbie" to VBa and have mostly been self-taught so please excuse my ignorance in this area.

    Thanks for any help provided.

    Gerald

  2. #2
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: VBA Selection error

    do u need to find the used last row in column A? if yes, try this
    Code:
    iFieldCount = wbSht.Cells(Rows.Count, "A").End(xlUp).Row
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    11

    Re: VBA Selection error

    Seenu,
    Your suggestion was right on and did the trick! However, I do need to select that range for a cut/paste operation. Here's the remainder of my code.

    wbSht.Range("A1").Select
    iFieldCount = wbSht.Cells(Rows.count, "A").End(xlUp).Row

    Selection.Cut
    Application.Goto Reference:="R9C8"
    wbSht.Paste
    Application.Goto Reference:="R1C2"
    wbSht.Range(ActiveSheet.Selection, ActiveSheet.Selection.End(xlDown)).Select
    Selection.Cut

    Application.Goto Reference:="R9C4"
    wbSht.Paste
    wbSht.Range("A1").Select


    I get the same error on the other Selection and Application statements. Thank you for the reply and any other tips would be much appreciated.

    Gerald

  4. #4
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: VBA Selection error

    this code cut the range A1 to last row of column A
    Code:
    iFieldCount = wbSht.Cells(Rows.count, "A").End(xlUp).Row
    wbSht.Range("A1:A" & iFieldCount ).Select
    Selection.Cut
    wbSht.Range("R9").Paste
    i m not sure wher u need to paste.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    11

    Re: VBA Selection error

    wbSht.Range("A":A" & iFieldCount).select seemed to work fine, but got the "object variable or with block variable not set" error on the Selection.Cut statement. Tried wbSht.Selection.Cut, but received a different error.

  6. #6
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,593

    Re: VBA Selection error

    alternate solution:

    vb Code:
    1. wbSht.Range("R9:R" & iFieldCount + 8).Value = wbSht.Range("A1:A" & iFieldCount ).Value
    2. wbSht.Range("A1:A" & iFieldCount ).Value = ""
    Last edited by kaliman79912; Jul 22nd, 2011 at 09:11 AM.
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    11

    Re: VBA Selection error

    Thanks for replying. I have no problem with the range selection, but with the Selection.Cut. This statement gives error "object variable or With block variable not set". Is there supposed to be an object specified with the Selection.Cut? If so, what would it be?

  8. #8
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: VBA Selection error

    hope this work
    Code:
    wbSht.Range("A1:A" & iFieldCount ).Cut
    i cudnt check, if posible attach ur file.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  9. #9

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Posts
    11

    Re: VBA Selection error

    Many thanks! That worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width