Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect Function error
Hi All,
I wrote the following code to hide certain columns and rows of data, then rename the file and save it to a directory. The code runs completely but I am getting an incorrect function error when it is finished. Any ideas what I can do to solve the problem? Sub WorkbookClose() ' ' WorkbookClose Macro ' Formats file for printing and saves to directory ' ' ' Hides Rows that are empty ' Dim rangeToTest As Range Dim anyCell As Object Set rangeToTest = Range("K13:K22") For Each anyCell In rangeToTest If IsEmpty(anyCell) Then anyCell.EntireRow.Hidden = True End If Next 'Hides columns containing raw/unadjusted data Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("J:U").Select Selection.EntireColumn.Hidden = True ChDir "G:\Compensation\Market Analysis Files\" mySerial = "" myPath = "G:\Compensation\Market Analysis Files\" myFile = Sheets("Market Detail").Range("C9") & " - " & Sheets("Market Detail").Range("C8") & " - " & Format(Date, "MM-DD-YYYY") myExt = ".xlsm" ' create output using sequence 1 to n if file already exists If Len(Dir(myPath & myFile & mySerial & myExt)) 0 Then Do While Len(Dir(myPath & myFile & mySerial & myExt)) 0 mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")" Loop End If ThisWorkbook.SaveAs Filename:=myPath & myFile & mySerial & myExt Range("H12").Select ThisWorkbook.Close End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect Function error
If the line that's getting the error is the .SaveAs, try adding the FileFormat:=
parameter to your code--and use the one that's associated with .xlsm files. (The extension has to match the fileformat.) If it's not that line, what line is it? cardfan3206 wrote: Hi All, I wrote the following code to hide certain columns and rows of data, then rename the file and save it to a directory. The code runs completely but I am getting an incorrect function error when it is finished. Any ideas what I can do to solve the problem? Sub WorkbookClose() ' ' WorkbookClose Macro ' Formats file for printing and saves to directory ' ' ' Hides Rows that are empty ' Dim rangeToTest As Range Dim anyCell As Object Set rangeToTest = Range("K13:K22") For Each anyCell In rangeToTest If IsEmpty(anyCell) Then anyCell.EntireRow.Hidden = True End If Next 'Hides columns containing raw/unadjusted data Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("J:U").Select Selection.EntireColumn.Hidden = True ChDir "G:\Compensation\Market Analysis Files\" mySerial = "" myPath = "G:\Compensation\Market Analysis Files\" myFile = Sheets("Market Detail").Range("C9") & " - " & Sheets("Market Detail").Range("C8") & " - " & Format(Date, "MM-DD-YYYY") myExt = ".xlsm" ' create output using sequence 1 to n if file already exists If Len(Dir(myPath & myFile & mySerial & myExt)) 0 Then Do While Len(Dir(myPath & myFile & mySerial & myExt)) 0 mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")" Loop End If ThisWorkbook.SaveAs Filename:=myPath & myFile & mySerial & myExt Range("H12").Select ThisWorkbook.Close End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect Function error
Thanks Dave. I tried to change the code to FileFormat instead of the myExt
code I have now and it didn't change anything. Unfortunately I can't seem to isolate where the problem is because I don't get any debug errors that take me into the code. The code simply executes and then as the file is closing, I get the incorrect function error. Any other ideas? "Dave Peterson" wrote: If the line that's getting the error is the .SaveAs, try adding the FileFormat:= parameter to your code--and use the one that's associated with .xlsm files. (The extension has to match the fileformat.) If it's not that line, what line is it? cardfan3206 wrote: Hi All, I wrote the following code to hide certain columns and rows of data, then rename the file and save it to a directory. The code runs completely but I am getting an incorrect function error when it is finished. Any ideas what I can do to solve the problem? Sub WorkbookClose() ' ' WorkbookClose Macro ' Formats file for printing and saves to directory ' ' ' Hides Rows that are empty ' Dim rangeToTest As Range Dim anyCell As Object Set rangeToTest = Range("K13:K22") For Each anyCell In rangeToTest If IsEmpty(anyCell) Then anyCell.EntireRow.Hidden = True End If Next 'Hides columns containing raw/unadjusted data Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("J:U").Select Selection.EntireColumn.Hidden = True ChDir "G:\Compensation\Market Analysis Files\" mySerial = "" myPath = "G:\Compensation\Market Analysis Files\" myFile = Sheets("Market Detail").Range("C9") & " - " & Sheets("Market Detail").Range("C8") & " - " & Format(Date, "MM-DD-YYYY") myExt = ".xlsm" ' create output using sequence 1 to n if file already exists If Len(Dir(myPath & myFile & mySerial & myExt)) 0 Then Do While Len(Dir(myPath & myFile & mySerial & myExt)) 0 mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")" Loop End If ThisWorkbook.SaveAs Filename:=myPath & myFile & mySerial & myExt Range("H12").Select ThisWorkbook.Close End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect Function error
Include both the extension and the fileformat in that line (if you didn't try
that already). You may want to share what you tried if that doesn't work. cardfan3206 wrote: Thanks Dave. I tried to change the code to FileFormat instead of the myExt code I have now and it didn't change anything. Unfortunately I can't seem to isolate where the problem is because I don't get any debug errors that take me into the code. The code simply executes and then as the file is closing, I get the incorrect function error. Any other ideas? "Dave Peterson" wrote: If the line that's getting the error is the .SaveAs, try adding the FileFormat:= parameter to your code--and use the one that's associated with .xlsm files. (The extension has to match the fileformat.) If it's not that line, what line is it? cardfan3206 wrote: Hi All, I wrote the following code to hide certain columns and rows of data, then rename the file and save it to a directory. The code runs completely but I am getting an incorrect function error when it is finished. Any ideas what I can do to solve the problem? Sub WorkbookClose() ' ' WorkbookClose Macro ' Formats file for printing and saves to directory ' ' ' Hides Rows that are empty ' Dim rangeToTest As Range Dim anyCell As Object Set rangeToTest = Range("K13:K22") For Each anyCell In rangeToTest If IsEmpty(anyCell) Then anyCell.EntireRow.Hidden = True End If Next 'Hides columns containing raw/unadjusted data Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("J:U").Select Selection.EntireColumn.Hidden = True ChDir "G:\Compensation\Market Analysis Files\" mySerial = "" myPath = "G:\Compensation\Market Analysis Files\" myFile = Sheets("Market Detail").Range("C9") & " - " & Sheets("Market Detail").Range("C8") & " - " & Format(Date, "MM-DD-YYYY") myExt = ".xlsm" ' create output using sequence 1 to n if file already exists If Len(Dir(myPath & myFile & mySerial & myExt)) 0 Then Do While Len(Dir(myPath & myFile & mySerial & myExt)) 0 mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")" Loop End If ThisWorkbook.SaveAs Filename:=myPath & myFile & mySerial & myExt Range("H12").Select ThisWorkbook.Close End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup incorrect #n/a error | Excel Discussion (Misc queries) | |||
Incorrect Function | Excel Programming | |||
Error - Incorrect Function | Excel Discussion (Misc queries) | |||
Incorrect Formula Error | Excel Discussion (Misc queries) | |||
RSQ function gives incorrect result | Excel Worksheet Functions |