Compile error, variable not defined
 

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!

Compile error, variable not defined
 

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!

Compile error, variable not defined
 

what about....

Set CurCol = ActiveSheet.Range.Column

:-D
does it work better ?

Compile error, variable not defined
 

ha, no it doesn't :-/

Compile error, variable not defined
 

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!

Compile error, variable not defined
 

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 ***