-
Jul 21st, 2011, 04:13 PM
#1
Thread Starter
New Member
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
-
Jul 21st, 2011, 09:28 PM
#2
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
-
Jul 22nd, 2011, 07:34 AM
#3
Thread Starter
New Member
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
-
Jul 22nd, 2011, 08:04 AM
#4
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.
-
Jul 22nd, 2011, 08:15 AM
#5
Thread Starter
New Member
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.
-
Jul 22nd, 2011, 09:06 AM
#6
Re: VBA Selection error
alternate solution:
vb Code:
wbSht.Range("R9:R" & iFieldCount + 8).Value = wbSht.Range("A1:A" & iFieldCount ).Value
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
-
Jul 22nd, 2011, 09:13 AM
#7
Thread Starter
New Member
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?
-
Jul 22nd, 2011, 10:05 AM
#8
Re: VBA Selection error
hope this work
Code:
wbSht.Range("A1:A" & iFieldCount ).Cut
i cudnt check, if posible attach ur file.
-
Jul 22nd, 2011, 12:07 PM
#9
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|