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" wrote in message
ups.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