Hello,

I need to run a macro on all the Cells of a sheet to run the PROPER function
on each cell and if the length of that Cell is zero to stick a blank space "
" inside the cell. Thru Help I find a sample and by simple modification I
inserted into my macro but deoes not work and gives me run time error 1004.
Here is my sample code:

Sub FixUp()
Dim myRange As Range
Set myRange = Worksheets("PtTable").Range("A1:AA500")
myRange.Formula = "=Proper()"
End Sub

Would you please help me on my problem?

--
Jeff B Paarsa

Sub FixUp()
Dim myRange As Range
dim cell as range
dim text as string
Set myRange = Worksheets("PtTable").Range("A1:AA500")
for each cell in myrange.cells
text = cell.value
if len(text)=0 then
cell.Value = " "
else
cell.value =WorksheetFunction.Proper(text)
end if
next
End Sub





"Jeffery B Paarsa" wrote in message
...
Hello,

I need to run a macro on all the Cells of a sheet to run the PROPER
function
on each cell and if the length of that Cell is zero to stick a blank space
"
" inside the cell. Thru Help I find a sample and by simple modification I
inserted into my macro but deoes not work and gives me run time error
1004.
Here is my sample code:

Sub FixUp()
Dim myRange As Range
Set myRange = Worksheets("PtTable").Range("A1:AA500")
myRange.Formula = "=Proper()"
End Sub

Would you please help me on my problem?

--
Jeff B Paarsa

Jeff,
The function call requires a parameter, viz =Proper(A1)
What you actually require is

Sub FixUp()
Dim myRange As Range
For Each myRange In Worksheets("PtTable").Range("A1:AA500")
If IsEmpty(myRange) Then
myRange.Value = " "
Else
myRange.Value =
Application.WorksheetFunction.Proper(myRange.value )
End If
Next myRange
End Sub



"Jeffery B Paarsa" wrote:

Hello,

I need to run a macro on all the Cells of a sheet to run the PROPER function
on each cell and if the length of that Cell is zero to stick a blank space "
" inside the cell. Thru Help I find a sample and by simple modification I
inserted into my macro but deoes not work and gives me run time error 1004.
Here is my sample code:

Sub FixUp()
Dim myRange As Range
Set myRange = Worksheets("PtTable").Range("A1:AA500")
myRange.Formula = "=Proper()"
End Sub

Would you please help me on my problem?

--
Jeff B Paarsa

Or, instead of jumping out to the worksheet function PROPER, you can use a
built-in VB function to do the same thing. Just replace this line of code...

myRange.Value = Application.WorksheetFunction.Proper(myRange.Value )

with this one...

myRange.Value = StrConv(myRange.Value, vbProperCase)

--
Rick (MVP - Excel)


"Kevin Beckham" wrote in message
...
Jeff,
The function call requires a parameter, viz =Proper(A1)
What you actually require is

Sub FixUp()
Dim myRange As Range
For Each myRange In Worksheets("PtTable").Range("A1:AA500")
If IsEmpty(myRange) Then
myRange.Value = " "
Else
myRange.Value =
Application.WorksheetFunction.Proper(myRange.value )
End If
Next myRange
End Sub



"Jeffery B Paarsa" wrote:

Hello,

I need to run a macro on all the Cells of a sheet to run the PROPER
function
on each cell and if the length of that Cell is zero to stick a blank
space "
" inside the cell. Thru Help I find a sample and by simple modification
I
inserted into my macro but deoes not work and gives me run time error
1004.
Here is my sample code:

Sub FixUp()
Dim myRange As Range
Set myRange = Worksheets("PtTable").Range("A1:AA500")
myRange.Formula = "=Proper()"
End Sub

Would you please help me on my problem?

--
Jeff B Paarsa