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?

Object variable issue
 

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?

Object variable issue
 

that worked. thanks