Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why Am I Getting the "Named Argument Not Found" Warning?
I've inherited the macro below. It prompts the user to enter a person's name
and a lesson number. (This is for keeping track of the use of proper names in a textbook development project.) It then looks on a master list of names on another sheet ("Alll Names") to see if it's approved for use. If so, it deposits some info in a few columns on the original sheet for tracking purposes. Sub ClassifyName() myRow = ActiveCell.Row CurrentSheet = ActiveSheet.Name UserInput$ = InputBox$("Name,Lesson") i = InStr(1, UserInput$, ",") If i 0 Then uName$ = Left$(UserInput$, i - 1) uLesson$ = Mid$(UserInput$, i + 1) ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("All Names").Select On Error GoTo NoSuchName Cells.Find(What:=uName$, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=False, SearchFormat:=False).Activate If Selection.Interior.ColorIndex = 46 Then MsgBox uName$ + " has been used previously." Exit Sub End If With Selection.Interior .ColorIndex = 46 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Selection.Offset(0, 1).Select EthnicGroup = Val(Selection.Offset(0, 0).Text) Selection.Offset(0, 1).Select SexIndicator = Val(Selection.Offset(0, 0).Text) Selection.Offset(0, 1).Select Sheets(CurrentSheet).Select ActiveCell.FormulaR1C1 = uLesson$ Worksheets(CurrentSheet).Cells(myRow, 2).Select ActiveCell.FormulaR1C1 = uName$ Worksheets(CurrentSheet).Cells(myRow, EthnicGroup).Select ActiveCell.FormulaR1C1 = 1 If SexIndicator 0 Then Worksheets(CurrentSheet).Cells(myRow, SexIndicator).Select ActiveCell.FormulaR1C1 = 1 End If Worksheets(CurrentSheet).Cells(myRow + 1, 1).Select Else MsgBox "Your input was invalid!" End If Exit Sub NoSuchName: MsgBox "Your name was not found!" End Sub When I try to run this, I get the awful named argument not found box. Help! Does anyone have any ideas? I'm trying to run this on a Mac, and the macro was originally used on a PC, but I honestly don't see what in this would cause my Mac to cry. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why Am I Getting the "Named Argument Not Found" Warning?
remove "SearchFormat:=False" -- likely the version you have on the Mac is
equivalent to xl2000 (which also croaks on this line, but is fine in XL2003) "wuddus" wrote in message ... I've inherited the macro below. It prompts the user to enter a person's name and a lesson number. (This is for keeping track of the use of proper names in a textbook development project.) It then looks on a master list of names on another sheet ("Alll Names") to see if it's approved for use. If so, it deposits some info in a few columns on the original sheet for tracking purposes. Sub ClassifyName() myRow = ActiveCell.Row CurrentSheet = ActiveSheet.Name UserInput$ = InputBox$("Name,Lesson") i = InStr(1, UserInput$, ",") If i 0 Then uName$ = Left$(UserInput$, i - 1) uLesson$ = Mid$(UserInput$, i + 1) ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("All Names").Select On Error GoTo NoSuchName Cells.Find(What:=uName$, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=False, SearchFormat:=False).Activate If Selection.Interior.ColorIndex = 46 Then MsgBox uName$ + " has been used previously." Exit Sub End If With Selection.Interior .ColorIndex = 46 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Selection.Offset(0, 1).Select EthnicGroup = Val(Selection.Offset(0, 0).Text) Selection.Offset(0, 1).Select SexIndicator = Val(Selection.Offset(0, 0).Text) Selection.Offset(0, 1).Select Sheets(CurrentSheet).Select ActiveCell.FormulaR1C1 = uLesson$ Worksheets(CurrentSheet).Cells(myRow, 2).Select ActiveCell.FormulaR1C1 = uName$ Worksheets(CurrentSheet).Cells(myRow, EthnicGroup).Select ActiveCell.FormulaR1C1 = 1 If SexIndicator 0 Then Worksheets(CurrentSheet).Cells(myRow, SexIndicator).Select ActiveCell.FormulaR1C1 = 1 End If Worksheets(CurrentSheet).Cells(myRow + 1, 1).Select Else MsgBox "Your input was invalid!" End If Exit Sub NoSuchName: MsgBox "Your name was not found!" End Sub When I try to run this, I get the awful named argument not found box. Help! Does anyone have any ideas? I'm trying to run this on a Mac, and the macro was originally used on a PC, but I honestly don't see what in this would cause my Mac to cry. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why Am I Getting the "Named Argument Not Found" Warning?
Thanks, Bob: That did the trick! I would NEVER have figured that out!
Sorry about the delayed response: I left work early yesterday and just saw your reply this morning. Many thanks! "Bob Umlas" wrote: remove "SearchFormat:=False" -- likely the version you have on the Mac is equivalent to xl2000 (which also croaks on this line, but is fine in XL2003) "wuddus" wrote in message ... I've inherited the macro below. It prompts the user to enter a person's name and a lesson number. (This is for keeping track of the use of proper names in a textbook development project.) It then looks on a master list of names on another sheet ("Alll Names") to see if it's approved for use. If so, it deposits some info in a few columns on the original sheet for tracking purposes. Sub ClassifyName() myRow = ActiveCell.Row CurrentSheet = ActiveSheet.Name UserInput$ = InputBox$("Name,Lesson") i = InStr(1, UserInput$, ",") If i 0 Then uName$ = Left$(UserInput$, i - 1) uLesson$ = Mid$(UserInput$, i + 1) ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("All Names").Select On Error GoTo NoSuchName Cells.Find(What:=uName$, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=False, SearchFormat:=False).Activate If Selection.Interior.ColorIndex = 46 Then MsgBox uName$ + " has been used previously." Exit Sub End If With Selection.Interior .ColorIndex = 46 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Selection.Offset(0, 1).Select EthnicGroup = Val(Selection.Offset(0, 0).Text) Selection.Offset(0, 1).Select SexIndicator = Val(Selection.Offset(0, 0).Text) Selection.Offset(0, 1).Select Sheets(CurrentSheet).Select ActiveCell.FormulaR1C1 = uLesson$ Worksheets(CurrentSheet).Cells(myRow, 2).Select ActiveCell.FormulaR1C1 = uName$ Worksheets(CurrentSheet).Cells(myRow, EthnicGroup).Select ActiveCell.FormulaR1C1 = 1 If SexIndicator 0 Then Worksheets(CurrentSheet).Cells(myRow, SexIndicator).Select ActiveCell.FormulaR1C1 = 1 End If Worksheets(CurrentSheet).Cells(myRow + 1, 1).Select Else MsgBox "Your input was invalid!" End If Exit Sub NoSuchName: MsgBox "Your name was not found!" End Sub When I try to run this, I get the awful named argument not found box. Help! Does anyone have any ideas? I'm trying to run this on a Mac, and the macro was originally used on a PC, but I honestly don't see what in this would cause my Mac to cry. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Value warning | Excel Worksheet Functions | |||
Warning about different sizes of paste areas | Excel Discussion (Misc queries) | |||
Need Function Argument | Excel Worksheet Functions | |||
Copy / Paste Warning | Excel Discussion (Misc queries) | |||
How do I set up an argument which asks for multiple criteria | Excel Discussion (Misc queries) |