Object variable or With block variable not set
 

I am working on the following routine and keep receiving an error about
an object variable or a with block variable not set. The error shows a
yellow highlight on the "RecordFound = FindRange.Find" line in the
routine. I have used this code in many other routines and it works
fine.

When a record is found, the routine works fine and the fields in the
spreadsheet are updated. When a record is not found, the error
happens. I am pulling my hair out.....




Private Sub Save_Click()
Dim RecordFound
Dim week As Integer
Dim rng As Range

GetSheet ("Team for Week")

Set FindRange = ActiveSheet.UsedRange

week = Dashboard.WeekNumber


RecordFound = FindRange.Find(What:=week, LookAt:=xlPart, _
LookIn:=xlValues, SearchOrder:=xlByColumns).Activate

If RecordFound = True Then
Cells(ActiveCell.Row, 1) = Dashboard.WeekNumber
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
Else
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
End If






End Sub

Object variable or With block variable not set
 

Give this a try...

Private Sub Save_Click()
Dim RecordFound as Range
Dim week As Integer
Dim rng As Range

GetSheet ("Team for Week")

Set FindRange = ActiveSheet.UsedRange

week = Dashboard.WeekNumber


set RecordFound = FindRange.Find(What:=week, LookAt:=xlPart, _
LookIn:=xlValues, SearchOrder:=xlByColumns)

If not RecordFound is nothing Then

--
HTH...

Jim Thomlinson


"Tom" wrote:

I am working on the following routine and keep receiving an error about
an object variable or a with block variable not set. The error shows a
yellow highlight on the "RecordFound = FindRange.Find" line in the
routine. I have used this code in many other routines and it works
fine.

When a record is found, the routine works fine and the fields in the
spreadsheet are updated. When a record is not found, the error
happens. I am pulling my hair out.....




Private Sub Save_Click()
Dim RecordFound
Dim week As Integer
Dim rng As Range

GetSheet ("Team for Week")

Set FindRange = ActiveSheet.UsedRange

week = Dashboard.WeekNumber


RecordFound = FindRange.Find(What:=week, LookAt:=xlPart, _
LookIn:=xlValues, SearchOrder:=xlByColumns).Activate

If RecordFound = True Then
Cells(ActiveCell.Row, 1) = Dashboard.WeekNumber
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
Else
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
End If






End Sub

Object variable or With block variable not set
 

Sorry in my previous post I forgot to add the line

RecordFound.Activate

as the first line after the if statement...
--
HTH...

Jim Thomlinson


"Tom" wrote:

I am working on the following routine and keep receiving an error about
an object variable or a with block variable not set. The error shows a
yellow highlight on the "RecordFound = FindRange.Find" line in the
routine. I have used this code in many other routines and it works
fine.

When a record is found, the routine works fine and the fields in the
spreadsheet are updated. When a record is not found, the error
happens. I am pulling my hair out.....




Private Sub Save_Click()
Dim RecordFound
Dim week As Integer
Dim rng As Range

GetSheet ("Team for Week")

Set FindRange = ActiveSheet.UsedRange

week = Dashboard.WeekNumber


RecordFound = FindRange.Find(What:=week, LookAt:=xlPart, _
LookIn:=xlValues, SearchOrder:=xlByColumns).Activate

If RecordFound = True Then
Cells(ActiveCell.Row, 1) = Dashboard.WeekNumber
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
Else
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
End If






End Sub

Object variable or With block variable not set
 

Hi Jim
thanks for the responses. I tried your modifications and am still
having the issue. I have tried many other variants and continue to
receive the same results. Any thoughts would be great.....

Object variable or With block variable not set
 

it's working now. thx