Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with Excel Macro. Unable to insert quotes before and after the text.
Hi help,
I am using Office Excel 2003. My requirement is something like this. I have a couple of rows that contain numeric data. I want to insert quotes before and after the numerics and a comma after each data. Its something like this. If the row has the value - 1234556 i need the output after running the macro to be '1234556', I have macro that is supposed to do this function, but when i run this macro on the worksheet it gives me the error - "runtime error 450 wrong number of arguments or invalid property assignment." Sub SKUlist() Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^p" .Replacement.Text = "',^p" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll With Selection.Find .Text = "^p" .Replacement.Text = "^p'" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll Selection.TypeText Text:="'" Selection.EndKey Unit:=wdStory Selection.TypeBackspace Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^p" .Replacement.Text = "^p'" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With ChangeFileOpenDirectory "C:\" ActiveDocument.SaveAs Filename:="Skulist.txt", FileFormat:=wdFormatText, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False, Encoding:=437, InsertLineBreaks:=False, AllowSubstitutions:=True, _ LineEnding:=wdCRLF End Sub Can someone please help me with this. Appreciate any help in advance. Thx Vamsi. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with Excel Macro. Unable to insert quotes before and after the text.
Your macro is a Word macro, not an XL macro.
IF you're trying to "run (the macro) on the worksheet" you need to use XL objects and methods. One way, based on what your requirements are, this might be a starting point: Public Sub Test() Dim rCell As Range For Each rCell In Selection With rCell If Not IsEmpty(.Value) Then _ If IsNumeric(.Value) Then _ .Value = "''" & .Text & "'," End With Next rCell End Sub In article , "Vamsi" wrote: Hi help, I am using Office Excel 2003. My requirement is something like this. I have a couple of rows that contain numeric data. I want to insert quotes before and after the numerics and a comma after each data. Its something like this. If the row has the value - 1234556 i need the output after running the macro to be '1234556', I have macro that is supposed to do this function, but when i run this macro on the worksheet it gives me the error - "runtime error 450 wrong number of arguments or invalid property assignment." Sub SKUlist() Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^p" .Replacement.Text = "',^p" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll With Selection.Find .Text = "^p" .Replacement.Text = "^p'" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll Selection.TypeText Text:="'" Selection.EndKey Unit:=wdStory Selection.TypeBackspace Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^p" .Replacement.Text = "^p'" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With ChangeFileOpenDirectory "C:\" ActiveDocument.SaveAs Filename:="Skulist.txt", FileFormat:=wdFormatText, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False, Encoding:=437, InsertLineBreaks:=False, AllowSubstitutions:=True, _ LineEnding:=wdCRLF End Sub Can someone please help me with this. Appreciate any help in advance. Thx Vamsi. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with Excel Macro. Unable to insert quotes before and after the text.
Hi JE McGimpsey,
Thank you very much for your response. I really appreciate your help with this. Thanks once again. Vamsi. -----Original Message----- Your macro is a Word macro, not an XL macro. IF you're trying to "run (the macro) on the worksheet" you need to use XL objects and methods. One way, based on what your requirements are, this might be a starting point: Public Sub Test() Dim rCell As Range For Each rCell In Selection With rCell If Not IsEmpty(.Value) Then _ If IsNumeric(.Value) Then _ .Value = "''" & .Text & "'," End With Next rCell End Sub In article , "Vamsi" wrote: Hi help, I am using Office Excel 2003. My requirement is something like this. I have a couple of rows that contain numeric data. I want to insert quotes before and after the numerics and a comma after each data. Its something like this. If the row has the value - 1234556 i need the output after running the macro to be '1234556', I have macro that is supposed to do this function, but when i run this macro on the worksheet it gives me the error - "runtime error 450 wrong number of arguments or invalid property assignment." Sub SKUlist() Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^p" .Replacement.Text = "',^p" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll With Selection.Find .Text = "^p" .Replacement.Text = "^p'" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll Selection.TypeText Text:="'" Selection.EndKey Unit:=wdStory Selection.TypeBackspace Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^p" .Replacement.Text = "^p'" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With ChangeFileOpenDirectory "C:\" ActiveDocument.SaveAs Filename:="Skulist.txt", FileFormat:=wdFormatText, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False, Encoding:=437, InsertLineBreaks:=False, AllowSubstitutions:=True, _ LineEnding:=wdCRLF End Sub Can someone please help me with this. Appreciate any help in advance. Thx Vamsi. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to access MSN Money Central Stock Quotes Add- In for Excel | Excel Discussion (Misc queries) | |||
when excel save as csv,double quotes in an cell issue | Excel Discussion (Misc queries) | |||
unable to delete or insert a text box ... | New Users to Excel | |||
How can I insert stock quotes in excel (not through msn)? | Excel Worksheet Functions | |||
don't insert quotes in excel save as tab-delim txt | Excel Discussion (Misc queries) |