Object variable issue

I am having difficulty with a routine that is trying to locate records
in a spreadsheet. I am receiving the error "Object variable or With
block variable not set" Error 91. I have no idea what this means.
Below is the routine. Please advise.


Private Sub Save_Click()

Dim FindRange As Range
Dim RecordFound As Range
Dim Week As Integer

GetSheet ("Team for Week")

Set FindRange = ActiveSheet.UsedRange
Week = Dashboard.WeekNumber

RecordFound = FindRange.Find(What:=Dashboard.WeekNumber,
LookAt:=xlPart, _
LookIn:=xlValues, SearchOrder:=xlByRows).Activate

If Not RecordFound Is Nothing Then
With Worksheets("Team for Week")
Set rng = .Cells(Rows.Count, "a").End(xlUp)(2)
.Cells(rng.Row, "a").Value = Week
.Cells(rng.Row, "b").Value = Dashboard.QBSelection
.Cells(rng.Row, "c").Value = Dashboard.RB1Selection
.Cells(rng.Row, "d").Value = Dashboard.RB2Selection
.Cells(rng.Row, "e").Value = Dashboard.WR1Selection
.Cells(rng.Row, "f").Value = Dashboard.WR2Selection
.Cells(rng.Row, "g").Value = Dashboard.TESelection
.Cells(rng.Row, "h").Value = Dashboard.KSelection
.Cells(rng.Row, "i").Value = Dashboard.DTSelection
End With

Else
Cells(ActiveCell.Row, 1) = Week
Cells(ActiveCell.Row, 2) = Dashboard.QBSelection
Cells(ActiveCell.Row, 3) = Dashboard.RB1Selection
Cells(ActiveCell.Row, 4) = Dashboard.RB2Selection
Cells(ActiveCell.Row, 5) = Dashboard.WR1Selection
Cells(ActiveCell.Row, 6) = Dashboard.WR2Selection
Cells(ActiveCell.Row, 7) = Dashboard.TESelection
Cells(ActiveCell.Row, 8) = Dashboard.KSelection
Cells(ActiveCell.Row, 9) = Dashboard.DTSelection
End If


End Sub


does the search variable in the Find statement have to be character?

Hi Tom,

An object variable assignment requires the Set statement.

Try changing:

> RecordFound = FindRange.Find(What:=Dashboard.WeekNumber,
> LookAt:=xlPart, _
> LookIn:=xlValues, SearchOrder:=xlByRows).Activate

to

Set RecordFound = FindRange.Find( _
What:=Week, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows)


> does the search variable in the Find statement have to be character?

No.

---
Regards,
Norman


"Tom" <torobins@yahoo.com> wrote in message
news:1136599958.034019.35470@g49g2000cwa.googlegroups.com...
>I am having difficulty with a routine that is trying to locate records
> in a spreadsheet. I am receiving the error "Object variable or With
> block variable not set" Error 91. I have no idea what this means.
> Below is the routine. Please advise.
>
>
> Private Sub Save_Click()
>
> Dim FindRange As Range
> Dim RecordFound As Range
> Dim Week As Integer
>
> GetSheet ("Team for Week")
>
> Set FindRange = ActiveSheet.UsedRange
> Week = Dashboard.WeekNumber
>
> RecordFound = FindRange.Find(What:=Dashboard.WeekNumber,
> LookAt:=xlPart, _
> LookIn:=xlValues, SearchOrder:=xlByRows).Activate
>
> If Not RecordFound Is Nothing Then
> With Worksheets("Team for Week")
> Set rng = .Cells(Rows.Count, "a").End(xlUp)(2)
> .Cells(rng.Row, "a").Value = Week
> .Cells(rng.Row, "b").Value = Dashboard.QBSelection
> .Cells(rng.Row, "c").Value = Dashboard.RB1Selection
> .Cells(rng.Row, "d").Value = Dashboard.RB2Selection
> .Cells(rng.Row, "e").Value = Dashboard.WR1Selection
> .Cells(rng.Row, "f").Value = Dashboard.WR2Selection
> .Cells(rng.Row, "g").Value = Dashboard.TESelection
> .Cells(rng.Row, "h").Value = Dashboard.KSelection
> .Cells(rng.Row, "i").Value = Dashboard.DTSelection
> End With
>
> Else
> Cells(ActiveCell.Row, 1) = Week
> Cells(ActiveCell.Row, 2) = Dashboard.QBSelection
> Cells(ActiveCell.Row, 3) = Dashboard.RB1Selection
> Cells(ActiveCell.Row, 4) = Dashboard.RB2Selection
> Cells(ActiveCell.Row, 5) = Dashboard.WR1Selection
> Cells(ActiveCell.Row, 6) = Dashboard.WR2Selection
> Cells(ActiveCell.Row, 7) = Dashboard.TESelection
> Cells(ActiveCell.Row, 8) = Dashboard.KSelection
> Cells(ActiveCell.Row, 9) = Dashboard.DTSelection
> End If
>
>
> End Sub
>
>
> does the search variable in the Find statement have to be character?
>

that worked. thanks