Copy formula doesn't work

2077delta

Board Regular
Joined
Feb 17, 2002
Messages
243
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]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Try changing this portion of your code to this syntax:
Code:
Case 2: Range("zbawkday2").Copy Cell2.Paste
to:
Code:
Case 2: Range("zbawkday2").Copy Destination:=Cell2
-Untested-
 

2077delta

Board Regular
Joined
Feb 17, 2002
Messages
243
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
2077delta

What other routine?

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

2077delta

Board Regular
Joined
Feb 17, 2002
Messages
243
Yes I do. In fact it is for the same worksheet I'm trying to copy the formulas on.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
Code:
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.
 

Forum statistics

Threads
1,136,878
Messages
5,678,326
Members
419,756
Latest member
vincent86kapelski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top