The macro below cleans up spreadsheets created by SPSS. It changes some
cell formatting and replaces a bunch of "#NULLS!" with blanks. But it
hangs up with a "Compile error, variable not defined" error at the line
indicated below. Since Worksheet is an object, not a variable, I don't
understand the message. I even looked it up in the Object Browser. So
why doesn't VBA recognize it? It even capitalized it when I entered it,
which I thought, meant it recognized it. But now it doesn't!

Sub SPSSDownloadCleanup()


Dim FoundCell As Range
Dim CurCol As Range

Set FoundCell = ActiveSheet.Range("a1:z1").find(What:="clientid",
LookIn:=xlFormulas)
If Not FoundCell Is Nothing Then
Set CurCol = Worksheet.Range.Column<----error here
CurCol.NumberFormat = "@"
CurCol.HorizontalAlignment = xlRight


Cells.Replace What:="#NULL!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Else
End If

Set FoundCell = ActiveSheet.Range("a1:z1").find(What:="stateid",
LookIn:=xlFormulas)
If Not FoundCell Is Nothing Then

Selection.HorizontalAlignment = xlCenter

Else
End If


End Sub

Does anyone see what I'm missing?
Thanks for the help!

You haven't specified which worksheet. You can use Activesheet,
Worksheets("sheet name"), or Worsksheets(n) where n is the index number.

--
HTH

Bob Phillips

"davegb" wrote in message
oups.com...
The macro below cleans up spreadsheets created by SPSS. It changes some
cell formatting and replaces a bunch of "#NULLS!" with blanks. But it
hangs up with a "Compile error, variable not defined" error at the line
indicated below. Since Worksheet is an object, not a variable, I don't
understand the message. I even looked it up in the Object Browser. So
why doesn't VBA recognize it? It even capitalized it when I entered it,
which I thought, meant it recognized it. But now it doesn't!

Sub SPSSDownloadCleanup()


Dim FoundCell As Range
Dim CurCol As Range

Set FoundCell = ActiveSheet.Range("a1:z1").find(What:="clientid",
LookIn:=xlFormulas)
If Not FoundCell Is Nothing Then
Set CurCol = Worksheet.Range.Column<----error here
CurCol.NumberFormat = "@"
CurCol.HorizontalAlignment = xlRight


Cells.Replace What:="#NULL!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Else
End If

Set FoundCell = ActiveSheet.Range("a1:z1").find(What:="stateid",
LookIn:=xlFormulas)
If Not FoundCell Is Nothing Then

Selection.HorizontalAlignment = xlCenter

Else
End If


End Sub

Does anyone see what I'm missing?
Thanks for the help!

what about....

Set CurCol = ActiveSheet.Range.Column

:-D
does it work better ?

ha, no it doesn't :-/

oh, you have the same problem with Range. If you want to refer to the find
results, just use

Set CurCol = cell.EntireColumn

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
You haven't specified which worksheet. You can use Activesheet,
Worksheets("sheet name"), or Worsksheets(n) where n is the index number.

--
HTH

Bob Phillips

"davegb" wrote in message
oups.com...
The macro below cleans up spreadsheets created by SPSS. It changes some
cell formatting and replaces a bunch of "#NULLS!" with blanks. But it
hangs up with a "Compile error, variable not defined" error at the line
indicated below. Since Worksheet is an object, not a variable, I don't
understand the message. I even looked it up in the Object Browser. So
why doesn't VBA recognize it? It even capitalized it when I entered it,
which I thought, meant it recognized it. But now it doesn't!

Sub SPSSDownloadCleanup()


Dim FoundCell As Range
Dim CurCol As Range

Set FoundCell = ActiveSheet.Range("a1:z1").find(What:="clientid",
LookIn:=xlFormulas)
If Not FoundCell Is Nothing Then
Set CurCol = Worksheet.Range.Column<----error here
CurCol.NumberFormat = "@"
CurCol.HorizontalAlignment = xlRight


Cells.Replace What:="#NULL!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Else
End If

Set FoundCell = ActiveSheet.Range("a1:z1").find(What:="stateid",
LookIn:=xlFormulas)
If Not FoundCell Is Nothing Then

Selection.HorizontalAlignment = xlCenter

Else
End If


End Sub

Does anyone see what I'm missing?
Thanks for the help!

If I understand your code right, you want the entire column formatted
correct? If so, change
Set CurCol = Worksheet.Range.Column, to
Set Curcol = ActiveCell.EntireColumn

Does that help?

*** Sent via Developersdex http://www.developersdex.com ***