PDA

View Full Version : Object Variable or With Block variable not set



Visual Newby
09-17-2013, 03:54 AM
Hi, I have written the code below but for some reason the line highlighted in bold gives the error "Object Variable or With Block variable not set". Any help on how to solve this would be much appreciated. Thanks in advance


Sub PreviousDay()
Dim CalcsNew As Worksheet
Set ClacsNew = ThisWorkbook.Worksheets("Calcs New")
Dim ContractSide As Range
With CalcsNew
Set ContractSide = .Range(CalcsNew.Range("D33"), CalcsNew.Range("D65000").End(xlUp))
For Each CellContractSide In ContractSide
If CellContractSide.Value = "Long" Then
CellContractSide.Offset(0, 7).Copy
CellContractSide.Offset(0, 9).PasteSpecial.Values
Exit For
End If
Next
For Each CellContractSide In ContractSide
If CellContractSide.Value = "Short" Then
CellContractSide.Offset(0, 7).Copy
CellContractSide.Offset(0, 9).PasteSpecial.Values
Exit For
End If
Next
End With
End Sub

GarysStudent
09-17-2013, 05:17 AM
Check your spelling:


ClacsNew
is not
CalcsNew

snb
09-17-2013, 07:29 AM
Sub PreviousDay()
For Each cl in ThisWorkbook.sheets("Calcs New").columns(4).specialcells(2)
If cl.row>32 and (cl = "Long" or cl="Short") Then cl.offset(,9)=cl.Offset(, 7).value
Next
End Sub

SamT
09-17-2013, 12:19 PM
CellContractSide.Offset(0, 9).PasteSpecial xlPasteValues