Compile Error: Named Argument Not Found???

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
Jimiskey was kind enough to provide me with this code, but I get a compile error stating that the named argument is not found with the SearchFormat text under highlights.

Any suggestion?

Thanks,

Anthony

Ok, try this code:

Sub InsertRows()
'
'Start at cell A1
Range("A1").Select
'Loop through finding the word Total and insert blank line afterwards
Do
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
'If you find Grand Total, stop you are done
If Left(ActiveCell.Value, 11) = "Grand Total" Then
Exit Sub
End If
Loop

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
I followed your advice and I'm no longer getting an error message but my spreadsheet is going haywire......How do I stop that?

Thanks,

Anthony
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
Andrew.........Also, I debugged the code and the following was highlighted:

ActiveCell.Offset(1, 0).Select

Thanks Again,

Anthony
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

What do you mean "my spreadsheet is going haywire"? Are your headings in column A? Are you on the right sheet when you run the code?

If you are getting an error on

ActiveCell.Offset(1, 0).Select

it's probably because you've reached line 65536.
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
Andrew....

It appears as if the code is continuously running and yes my first header is in Cell A1 along with the fact that all of the "Totals" including "Grand Total" are in column A.

I'm still learning how to understand code so I cannot see where the problem lies.

Any more suggestions........?

Thank you for your patience.

Anthony
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Well, Grand Total includes the word Total, so it never gets found because we have already inserted a row and moved down. So the test for Grand Total needs to be moved:

Code:
Sub InsertRows()
'   Start at cell A1
    Range("A1").Select
'   Loop through finding the word Total and insert blank line afterwards
    Do
        Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
'       If you find Grand Total, stop you are done
        If Left(ActiveCell.Value, 11) = "Grand Total" Then
            Exit Sub
        End If
        ActiveCell.Offset(1, 0).Select
        Selection.EntireRow.Insert
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
Andrew:

Worked great..........just one more question and not that it really matters, but it gave me two blank lines instead of one. Any reason?

Many, many thanks!!

Anthony
 

puppy26

New Member
Joined
Feb 23, 2012
Messages
42
Hi Experts,
I'm trying to write a code to import the excel file into access. Here is the code..

Private Sub Command8_Click()
Dim strPathFile As Variant
Dim strFilter As String
Dim ahtAddFilterItem() As String
Dim ahtCommonFileOpenSave() As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.XLS")
strPathFile = ahtCommonFileOpenSave(InitialDir:="C:\Users\PZ8P95\Desktop\Excel testing\", _
Filter:=strFilter, OpenFile:=False, Flags:=lngFlags, _
DialogTitle:="Select the EXCEL file:") ---------------> In this line, I'm getting Compile error as Named arguements not allowed

If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If
DoCmd.TransferSpreadsheet acImport, (strFilter \ "Excel file.xls"), _
Sheet1, strPathFile, False
End Sub

Please let me know what's the solution Please.
 

Forum statistics

Threads
1,143,923
Messages
5,721,557
Members
422,370
Latest member
A Nonomus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top