Copy formula doesn't work

[2077delta]

I have the following code

Sub zbaesttest()

Dim day As Integer

Set Cell1 = Range("A8").End(xlDown)
Set Cell2 = Range("A8").End(xlDown).Offset(0, 10)

'Test for ZBA Estimate formula
'If no formula then copy appropriate weekday ZBA formula to cell
If Cell2.HasFormula = False Then _
day = Application.WorksheetFunction.Weekday(Cell1)
Select Case day
Case 1: Exit Sub
Case 2: Range("zbawkday2").Copy Cell2.Paste
Case 3: Range("zbawkday3").Copy Cell2.Paste
Case 4: Range("zbawkday4").Copy Cell2.Paste
Case 5: Range("zbawkday5").Copy Cell2.Paste
Case 6: Range("zbawkday6").Copy Cell2.Paste
Case 7: Exit Sub
End Select

End If

What this is supposed to do is look at Cell2. If there is no formula in Cell2, it copies a formula into Cell2 based on the weekday found in the date for Cell1. However when I run it I keep getting a Run Time Error 438: Object doesn't support this property. What exactly does this mean? Does it have to do with how I'm trying to paste the formula?

End Sub[/code]

 

[Datsmart]

Try changing this portion of your code to this syntax:

Case 2: Range("zbawkday2").Copy Cell2.Paste

to:

Case 2: Range("zbawkday2").Copy Destination:=Cell2

-Untested-

 

[2077delta]

Thank you, but that didn't work. Not only did it not work, when it executed that code instead of exiting out of the routine it jumped to another routine.

 

[Norie]

2077delta

What other routine?

Do you have code that is triggered by a change in a worksheet?

 

[2077delta]

Yes I do. In fact it is for the same worksheet I'm trying to copy the formulas on.

 

[Datsmart]

You will need to turn OFF events as your code runs, then turn events back on. The code below shows an example how this is done.

Sub zbaesttest()

Dim day As Integer
    
    'Turn OFF Events
    Application.EnableEvents = False

Set Cell1 = Range("A8").End(xlDown)
Set Cell2 = Range("A8").End(xlDown).Offset(0, 10)

'Test for ZBA Estimate formula
'If no formula then copy appropriate weekday ZBA formula to cell
    If Cell2.HasFormula = False Then _
    day = Application.WorksheetFunction.Weekday(Cell1)
        Select Case day
            Case 1: GoTo Continue
            Case 2: Range("zbawkday2").Copy Destination:=Cell2
            Case 3: Range("zbawkday3").Copy Destination:=Cell2
            Case 4: Range("zbawkday4").Copy Destination:=Cell2
            Case 5: Range("zbawkday5").Copy Destination:=Cell2
            Case 6: Range("zbawkday6").Copy Destination:=Cell2
            Case 7: GoTo Continue
        End Select

Continue:
    'Turn Events back ON
    Application.EnableEvents = True

End Sub

Your code does not assign the variable you use, "zbawkday#", a value. The above code worked for me when I set that variable as a valid cell address.