Object variable or With block variable not set - error

[chipsworld]

Hoping someone can see the error of my ways in the below.

THis designed to do two things...

1. If the record already exists, I simply updates it (works fine)
2. If the record doesn't exist, write it the historical sheet. (Doesn't work. Gives the "Object variable or With block variable not set" error.

Here is my code:

Private Sub CommandButton1_Click()
Dim response As Integer
Dim lkup As String
Dim SourceWS    As Worksheet, DestWS As Worksheet
Dim SourceRng   As Range, DestCell   As Range
Dim lloop As Long
Set SourceWS = Sheets("Leave Calculations") ' Source Sheet
Set DestWS = Sheets("Historical") 'Destination Sheet
lkup = Sheets("Formulas").Range("V5").Value



response = MsgBox("Are you ready to print?", vbYesNo, "PRINT SHEET?")
    If response = 6 Then
    
    'Application.Dialogs(xlDialogPrinterSetup).Show
    
    'ActiveSheet.PrintOut
    With Worksheets("historical")
        Dim Rw As Integer
    
         [I][B]Rw = .Range("B:B").Find(lkup, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious).Row[/B][/I] 'This is where the Debugger highlights



 
    
    ' Copy data to Historical form

 
    Application.ScreenUpdating = 0
    
        With SourceWS
        Set DestCell = DestWS.Range("a" & Rows.Count).End(xlUp).Offset(1)
        For lloop = 1 To 16 ' number must match total cells in range
            Set SourceRng = Choose(lloop, Sheets("Formulas").Range("v4"), Sheets("Formulas").Range("v5"), Sheets("Formulas").Range("v2"), Sheets("Leave Calculations").Range("b6"), Sheets("Leave Calculations").Range("c6"), _
            Sheets("Leave Calculations").Range("d6"), Sheets("Leave Calculations").Range("d11"), Sheets("Formulas").Range("v3"), Sheets("Leave Calculations").Range("e15"), Sheets("Leave Calculations").Range("e16"), Sheets("Leave Calculations").Range("e21"), _
            Sheets("Formulas").Range("b39"), Sheets("Formulas").Range("b57"), Sheets("Formulas").Range("c57"), Sheets("Formulas").Range("V10"), Sheets("Formulas").Range("B1")) 'adjust the range
            SourceRng.Copy
            DestCell.Offset(, lloop - 1).PasteSpecial xlPasteValues
        Next lloop
        End With
    
            With Application
                .CutCopyMode = 0
                .ScreenUpdating = 0
            End With
 Else:
 
 
        With DestWS
         .Range("A" & Rw).Value = Sheets("Formulas").Range("V4").Value
         .Range("B" & Rw).Value = Sheets("Formulas").Range("V5").Value
         .Range("D" & Rw).Value = Sheets("Formulas").Range("V6").Value
         .Range("E" & Rw).Value = Sheets("Formulas").Range("V7").Value
         .Range("F" & Rw).Value = Sheets("Formulas").Range("V8").Value
         .Range("G" & Rw).Value = Sheets("Formulas").Range("v9").Value
         .Range("I" & Rw).Value = Sheets("Formulas").Range("V11").Value
         .Range("J" & Rw).Value = Sheets("Formulas").Range("V12").Value
         .Range("H" & Rw).Value = Sheets("Formulas").Range("V3").Value
         .Range("K" & Rw).Value = Sheets("Formulas").Range("V13").Value
         .Range("C" & Rw).Value = Sheets("Formulas").Range("V2").Value
         .Range("O" & Rw).Value = Sheets("Formulas").Range("V10").Value
       End With
 
 End If
 
 
  
    
    End With
    
    
    
    
    If response = 7 Then
    
    Sheets("calc sheet").Select
   
    End If

    End If
    
   
    
ThisWorkbook.Save
    

Sheets("Leave Calculations").Visible = True
Sheets("Leave Calculations").Select
Sheets("calc sheet").Visible = False
Sheet1.CommandButton1.Activate



End Sub
  /CODE]

HELP! I am going crazy trying to figure this out

 

[chipsworld]

I realize that I have the printing turned off. this is so that I don't waste reams of paper! LOL

 

[Joe4]

You will get that error message if it cannot find the value that it is looking for. You may want to add some error code to handle that.

 

[JoeMo]

Possibly, the lkup value is not found in col B. I would suggest you rewrite the bold line bit like this to make it easier to diagnose the error:
Note that Rw is dimensioned as a long as it is possible that it's position is beyond the max limit of an integer variable.

With Worksheets("historical")
        Dim Rw As Long, Fnd as range
    
         Fnd = .Range("B:B").Find(lkup, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
if not Fnd is Nothing then
Rw = Fnd.Row
Else
Msgbox lkup & " not found in column B"
End If
'rest of code

If you get the same error, you will be able to scan col B and see if it contains lkup. If you think you see it there, replace it with: Sheets("Formulas").Range("V5").Value and see if that fixes the problem.

 

[chipsworld]

You will get that error message if it cannot find the value that it is looking for. You may want to add some error code to handle that.

OMG..I can't believe I forgot to add error handing...

Fixed it perfectly

Thank you so very much!!!

 

[Joe4]

You are welcome.

JoeMo posted a nice way to handle that situation.

 

[JoeMo]

You are welcome.

JoeMo posted a nice way to handle that situation.

Thanks Joe4, this reminded me that I need to make a correction to that post for an oversight: since Fnd is a range object it needs to be set. The correction is shown in bold red below.

With Worksheets("historical")
             Dim Rw As Long, Fnd As Range
              Set Fnd = .Range("B:B").Find(lkup, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
     If Not Fnd Is Nothing Then
         Rw = Fnd.Row
     Else
         MsgBox lkup & " not found in column B"
     End If
'rest of code