block variable not set?

[davegb]

The following is giving me a "block variable not set" error where I've
marked:

Sub ChngHeader()

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

Set wbkNewHdr = Workbooks("Personal.xls")
Set wbkData = ActiveWorkbook
ActiveSheet.Range("A1").Select
Set rTtl = Range(Selection, Selection.End(xlToRight))

Set rfoundhd = rTtl.Find("stcounty", LookIn:=xlValues)
rfoundhd.Value = "County of Inv"<--ERR0R

rfoundhd is publicly declared as a range. Any ideas?
Thanks!

 

[Chip Pearson]

The cause of this is that your Find method on the previous line
of code did not find what it was looking for.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[davegb]

Thanks, Chip. Strange error message for not finding anything!
I added a test line to detect if the find didn't find anything, and it
works great!

 

[BBert]

The cause of this is that your Find method on the previous line
of code did not find what it was looking for.

So you can try this:
******
Sub ChngHeader()

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

Set wbkNewHdr = Workbooks("Personal.xls")
Set wbkData = ActiveWorkbook
ActiveSheet.Range("A1").Select
Set rTtl = Range(Selection, Selection.End(xlToRight))

Set rfoundhd = rTtl.Find("stcounty", LookIn:=xlValues)

If Not rfoundhd Is Nothing Then
rfoundhd.Value = "County of Inv" '< --ERR0R
End If

End Sub
******
--
Met vriendelijke groeten / Mit freundlichen Grüßen / With kind
regards/Avec mes meilleures salutations
BBert

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"

 

[Chip Pearson]

Strange error message for not finding anything!

It's not so strange a message if you think about it. You Set the
variable to the result of the Find. Find must indicate its
results somehow, so it sets the result variable to Nothing if the
find fails. Typically, you write such code as


Dim FoundCell As Range
Set FoundCell = WhateverRange.Find(....)
If FoundCell Is Nothing Then
' code for not found
Else
' code for found
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com