Yet another error

[davegb]

This program formats and cleans up data coming in from SPSS (Statistics
software). It calls a couple of other programs. When it calls Change
Header, it hangs on the line noted below. Both variable are declared,
wkbData as a public workbook, rTtl as a public range. Both are set. So
why am I getting an "Application defined or object defined error at the
indicated line?


Option Explicit
Public wbkData As Workbook
Public rFoundHd As Range
Public rTtl As Range
Sub SPSSClean()
Dim myCell As Range
Dim CurCol As Range
Dim FoundCell As Range


Application.ScreenUpdating = False
Range("a1").Select
Selection.CurrentRegion.Select

Clean

'Change from SPSS headers to understandable headers
ChngHeader

Range("a1").Select

Set rTtl = Range(Selection, Selection.End(xlToRight))

For Each rFoundHd In rTtl.Cells
If Right(rFoundHd.Value, 2) = "id" Then
ActiveSheet.Range(rFoundHd,
rFoundHd.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlRight
.NumberFormat = "@"
End With
End If
Next rFoundHd

'Replace #NULL! with empty cell
Cells.Replace What:="#NULL!", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

'find Worker Name and run ProperCase on that column
Set rFoundHd = rTtl.Find("Worker Name", _
LookIn:=xlValues)
If Not rFoundHd Is Nothing Then
ActiveSheet.Range(rFoundHd, rFoundHd.End(xlDown)).Select
ProperCase

End If

Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter

End With

Application.ScreenUpdating = True

End Sub


Sub ChngHeader()

Dim rOldHds As Range
Dim cell As Range
Dim strOldHd As String
Dim strNewHd As String
Dim lEndRow As Long
Dim wbkNewHdr As Workbook

Set wbkNewHdr = Workbooks("Test Overall Statewide and County
Percentages.xls")
Set wbkData = Workbooks("Reunification Exit.xls")
ActiveSheet.Range("A1").Select
Set rTtl = Range(Selection, Selection.End(xlToRight))


lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count,
2).End(xlUp).Row
With wbkNewHdr.Sheets("Macro Records")
Set rOldHds = Range(.Cells(40, 2), .Cells(lEndRow, 2))
End With

For Each cell In rOldHds
strOldHd = cell.Value
strNewHd = cell.Offset(0, 1).Value


ERROR-->Set rFoundHd =
wbkData.Worksheets(1).Range(rTtl).Find(strOldHd, LookIn:=xlValues)

If Not rFoundHd Is Nothing Then
rFoundHd.Value = strNewHd
End If
Next cell

End Sub

Thanks again!

 

[K Dales]

If rTtl is a range, you don't need Range(rTtl), just rTtl itself:
Set rFoundHd =
rTtl.Find(strOldHd, LookIn:=xlValues)

 

[Tom Ogilvy]

You
Set rTtl = Range(Selection, Selection.End(xlToRight))

so rTtl is a range reference at this point. then you try to use it as a
string in the offending line of code.

 

[davegb]

Thanks, both of you!

You
Set rTtl = Range(Selection, Selection.End(xlToRight))

so rTtl is a range reference at this point. then you try to use it as a
string in the offending line of code.