Compile Error: Named Argument Not Found???

[Anthony G.]

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

 

[Andrew Poulsom]

It's probably new to XL 2002. Just delete:

, SearchFormat:=False

 

[Anthony G.]

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.]

Andrew.........Also, I debugged the code and the following was highlighted:

ActiveCell.Offset(1, 0).Select

Thanks Again,

Anthony

 

[Andrew Poulsom]

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.]

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]

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:

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.]

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

 

[Andrew Poulsom]

It gave me one blank line. Perhaps you ran it twice.

 

[puppy26]

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.