Message box if nil in code

[cfer]

Hi,

I have the following code,

if there is a zero in the range M1, I want to be able to halt the code, and a Pop up message, There is no data for this range.

Sub CopyFormula()

Worksheets("Finance").Select
NumberOfCopies = Sheets("Details").Range("M1").Value
With Worksheets("Finance").Range("C8:M8")
.Copy .Offset(1).Resize(NumberOfCopies, 1)
End With

Any help appreciatted.

Cfer

End Sub

 

[VoG]

Hi. Try

Sub CopyFormula()
If Sheets("Details").Range("M1").Value = 0 Then
    MsgBox "No data"
    Exit Sub
End If
Worksheets("Finance").Select
NumberOfCopies = Sheets("Details").Range("M1").Value
With Worksheets("Finance").Range("C8:M8")
.Copy .Offset(1).Resize(NumberOfCopies, 1)
End With

 

[Peter_SSs]

Another possible way to go ..

Sub CopyFormula()
    NumberOfCopies = Sheets("Details").Range("M1").Value
    Select Case NumberOfCopies
        Case 0
            MsgBox "No data"
        Case Is > 0
            With Worksheets("Finance").Range("C8:M8")
                .Copy .Offset(1).Resize(NumberOfCopies)
            End With
    End Select
End Sub

 

[cfer]

Hi Vogg,

Thanks

Worked just fine.

Cfer