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
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