Microsoft Excel - Compile error - wrong number of arguments or invalid property assignment

Asked By John Wirth on 28-Apr-13 08:19 AM
My code breaks at the section marked in bold. I am using a cell value for the column number of a cell. The cell value is a whole number- I have checked it.

Sub Tasking_Import()
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Set wdApp = Word.Application
Dim rFoundCell As Range
Dim rStr As String, Ronly As String
If wdApp.Documents.Count > 1 Then
End If
If wdApp.ActiveDocument.Tables.Count < 1 Then
Exit Sub
End If
On Error Resume Next
rStr = wdApp.ActiveDocument.FormFields(22).Range.Text
Ronly = Replace(rStr, Chr(13), Chr(10))
            Set rFoundCell = (Columns(2).Find(What:=Extract_Number_from_Text(wdApp.ActiveDocument.FormFields(1).Range.Text), _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False))
Cells(rFoundCell.Row, Cells(1, 50).Value).Value = Replace(wdApp.ActiveDocument.FormFields(20).Result, Chr(13), Chr(10))
Cells(rFoundCell.Row, Cells(1, 50).Value + 1).Value = Application.WorksheetFunction.Trim(Ronly)
Cells(rFoundCell.Row, Cells(1, 50).Value + 2).Value = wdApp.ActiveDocument.FormFields(21).Result
Cells(rFoundCell.Row, Cells(1, 50).Value + 3).Value = wdApp.ActiveDocument.FormFields(19).Result
Harry Boughen replied to John Wirth on 28-Apr-13 06:16 PM
Hi John,
Can't see any obvious faults with your code.  A bit hard to test anything without knowing the ins and outs of both docs. 
Are you sure that it is the Cells(1,50).Value that is the problem?
Have you had a look at the various values that you are using with a debug watch to make sure that they are all valid.  For instance is the value of rFoundCell.Row what you think it is, etc?
Regards
Harry
Harry Boughen replied to John Wirth on 28-Apr-13 06:36 PM
Hello again John,
Just realised that you are getting a compile error and so my comments won't be relevant.  Sorry about that.
This is just a wild guess. Try doing the replace function to another variable similar to what you do for Ronly.
Regards
Harry
Harry Boughen replied to John Wirth on 28-Apr-13 07:23 PM
And again John,
I have faked up a spreadsheet to test just the part of the code that appears to be a problem and it works just fine so I am wondering whether it is the Replace function that is causing the trip up.  I notice that when you determine rStr you use .Text but in this one you use .Result.
Regards
Harry
John Wirth replied to Harry Boughen on 29-Apr-13 08:12 AM
Thanks Harry. I have just tried stripping right down to the code below and I still get the same error:

Cells(1, 2).Value = "Replace"
John Wirth replied to Harry Boughen on 29-Apr-13 08:22 AM
I think I have fixed t, but don't understand why using "cells" throws up the error and needs to be prefixed by "ActiveSheet"; never had this problem before when using "cells":

ActiveSheet.Cells(rFoundCell.Row, ActiveSheet.Cells(1, 50).Value).Value = Replace(wdApp.ActiveDocument.FormFields(20).Result, Chr(13), Chr(10))
ActiveSheet.Cells(rFoundCell.Row, ActiveSheet.Cells(1, 50).Value + 1).Value = Application.WorksheetFunction.Trim(Ronly)
ActiveSheet.Cells(rFoundCell.Row, ActiveSheet.Cells(1, 50).Value + 2).Value = wdApp.ActiveDocument.FormFields(21).Result
ActiveSheet.Cells(rFoundCell.Row, ActiveSheet.Cells(1, 50).Value + 3).Value = wdApp.ActiveDocument.FormFields(19).Result
Harry Boughen replied to John Wirth on 29-Apr-13 05:11 PM
Hi John,
Interesting that.  That thought had occurred to me but when it worked OK for me without I wrote it off as an idea.  I wonder whether it is to do with the fact that it needs to distinguish between whether it is Excel or Word for some reason.
Glad you solved it.
Harry
John Wirth replied to Harry Boughen on 30-Apr-13 06:41 PM
Thanks Harry, your post led me in the right direction of analysing the problem.  I wondered the same re Excel/ Word. Referencing 'Cells' works fine if there are no references to Word. I have found similar instances of this in the past.