Issue with Excel Macro. Unable to insert quotes before and after the text.

[Vamsi]

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.

 

[JE McGimpsey]

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

 

[Vamsi]

Hi JE McGimpsey,

Thank you very much for your response.
I really appreciate your help with this.

Thanks once again.
Vamsi.