Named argument not found vba ошибка

Access 2016.
подключаюсь к Excel

Private Sub btnFile_Click()
Dim appExcel As Object
Dim a as range
Dim wbk As Variant 'Excel.Workbook
Dim wks As Variant 'Excel.Worksheet
Set appExcel = CreateObject("Excel.Application")
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Sheets.Add(After:=wbk.Sheets(wbk.Sheets.Count))
Set wks = wbk.Sheets("Bilans")
With wks
For col = 2 To 5
        Set a = wks.Range("B" & col & ":Y" & col)
       maxValue = appExcel.WorksheetFunction.Max(a)
       avgValue = appExcel.WorksheetFunction.Average(a)
       a.Replace What:=maxValue, _ 
       replacement:=Replace(CDbl(avgValue), ".", ","), LookAt:=xlWhole, _ 
       SearchOrder:=xlByRows, MatchCase:=False
Next col

на моменте a.Replaceполучаю ошибку:
Named argument not found
Как пофиксить?

upd

Private Sub btnFile_Click()
Dim appExcel As Excel.Application
Dim a as range
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Set appExcel = New Excel.Application
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Sheets.Add(After:=wbk.Sheets(wbk.Sheets.Count))

на wbk.Sheets.Countошибка Method or data member not found.

Что смущает, так это то что месяц назад база работала.

• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

detailsSheet.Cells.Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

detailsSheet.Cells.Copy
detailsSheet.PasteSpecial Paste:=xlPasteValues

detailsSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

detailsSheet.PasteSpecial Paste:=xlPasteValues

Sub Remove_Formulas()

With Sheets("detailsSheet").UsedRange
        .Value = .Value
    End With
End Sub

When working with tables in VBA Word, you may encounter the ‘Named Argument Not Found’ error when trying to create a range from cells within a table. This error is usually caused by not specifying the correct arguments for the range object.

To create a range from cells within a table, the VBA code should include the following arguments:

• StartRange: The starting cell of the range. This can be either a cell reference or a numeric index.
• EndRange: The ending cell of the range. This can also be either a cell reference or a numeric index.
• Table: The table where the cells are located.

However, if any of these arguments are missing or incorrect, the ‘Named Argument Not Found’ error will occur.

To solve this error, ensure that all three arguments are properly specified in your VBA code. Here’s an example code that creates a range from cells B2 to C4 in the first table of the Word document:

Sub CreateRangeFromTable()
    'Specify the range arguments
    StartRange = ActiveDocument.Tables(1).Cell(2, 2)
    EndRange = ActiveDocument.Tables(1).Cell(4, 3)
    Table = ActiveDocument.Tables(1)
    
    'Create the range object
    Set MyRange = Table.Range(Start:=StartRange.Range, End:=EndRange.Range)
End Sub

In this code, the StartRange argument is specified as the cell located in row 2, column 2 (cell B2), while the EndRange argument is specified as the cell in row 4, column 3 (cell C4). The Table argument is set to the first table in the Word document.

By specifying all three arguments correctly, the range object is created without triggering the ‘Named Argument Not Found’ error.

In summary, the ‘Named Argument Not Found’ error in VBA Word can be resolved by ensuring that all required arguments are properly specified for the range object. By following the correct syntax and referencing the correct cells and tables, you can create ranges from cells within tables seamlessly.

1. 05-06-2011, 03:06 PM

   
   

   #1

   Named Argument not found
   

   When I run this code, I get «Named Argument Not Found» with «Type» highlighted on the debugging screen. What’s wrong? With Number DIM as Integer will this code prevent people from closing the input box without entering anything? Thanks

   Last edited by ChemistB; 05-06-2011 at 03:48 PM.

   ChemistB
   My 2?

   substitute commas with semi-colons if your region settings requires

   Don’t forget to mark threads as «Solved» (Edit First post>Advanced>Change Prefix)
   If I helped, Don’t forget to add to my reputation (click on the little star at bottom of this post)

   Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

   ---

2. 05-06-2011, 03:27 PM

   
   

   #2

   Re: Named Argument not found
   

   There are two kinds of input boxes immediately available in the Excel VBIDE, so you have to be a little careful.

   VBA.InputBox
   Application.InputBox

   The VBA Inputbox does not have a Type parameter — and that is the InputBox you are calling — so you get an error. You want the Application.InputBox.

   ---

3. 05-06-2011, 03:30 PM

   
   

   #3

   Re: Named Argument not found
   

   Well, that helps. LOL Thanks Colin.

   ---

4. 05-06-2011, 03:51 PM

   
   

   #4

   Re: Named Argument not found
   

   But it’s rather odd to use an inputbox if you also use userforms.

   You can use 2 frames in a userform. At the start the second one is hidden.
   In the first one the user can enter the number of teams (preferably using a spinbutton (min1 max 6))
   After exiting the spinbutten the second frame can be shown with as many textboxes as indicated by the spinbutton.

   ---

   • Arrays _ _ _ Dictionaries
   • Avoid using VBA-code you do not understand.

   ---

5. 05-06-2011, 04:08 PM

   
   

   #5

   Re: Named Argument not found
   

   Thanks snb. I will play with that until I am comfortable with it.

   ---