Run-time error '1004': Application-defined or object-defined error

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
223
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm getting an error in my code and I can figure out why.
Error is here:
VBA Code:
            rngCogsR6.Offset(0, 2).Formula = "=$0$26*$R31"
            rngCogsR7.Offset(0, 2).Formula = "=$0$26*$R32"

But these lines work

Code:
            rngCogsR6.Offset(0, 3).Formula = "=$P$26*$R31"
            rngCogsR7.Offset(0, 3).Formula = "=$P$26*$R32"
            
        'CPM
            Debug.Print rngCogsR6.Offset(0, 2).Address

Any idea what might be going on - been looking at this for several hours
Full code below.

Thanks,
w

Code:
Option Explicit

Public Sub Updatex()

    'Objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rngCogsR1 As Range
        Dim rngCogsR2 As Range
        Dim rngCogsR3 As Range
        Dim rngCogsR4 As Range
        Dim rngCogsR5 As Range
        Dim rngCogsR6 As Range
        Dim rngCogsR7 As Range
        Dim rngCogsR8 As Range
        Dim rngCogsR9 As Range
        Dim rngCogsR10 As Range
        
    'Variables
        Dim bProtectionStatus As Boolean
        
    'Initialize objects
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Model")
        
    'Worksheet protection
        'If the worksheet is protected, unprotect it, else do nothing
            bProtectionStatus = GetProtectionStatus(ws:=ws)
            If bProtectionStatus Then
                ws.Unprotect Password:="stuff"
            End If

        With ws
            Set rngCogsR1 = .Range(.Cells(26, 6), .Cells(26, 6))
            Set rngCogsR2 = .Range(.Cells(27, 6), .Cells(27, 6))
            Set rngCogsR3 = .Range(.Cells(28, 6), .Cells(28, 6))
            Set rngCogsR4 = .Range(.Cells(29, 6), .Cells(29, 6))
            Set rngCogsR5 = .Range(.Cells(30, 6), .Cells(30, 6))
            Set rngCogsR6 = .Range(.Cells(31, 6), .Cells(31, 6))
            Set rngCogsR7 = .Range(.Cells(32, 6), .Cells(32, 6))
            Set rngCogsR8 = .Range(.Cells(33, 6), .Cells(33, 6))
            Set rngCogsR9 = .Range(.Cells(34, 6), .Cells(34, 6))
            Set rngCogsR10 = .Range(.Cells(35, 6), .Cells(35, 6))
        End With
        
    'Set Cogs Labels
        rngCogsR1.Value = "aa"
        rngCogsR2.Value = "bb"
        rngCogsR3.Value = "cc"
        rngCogsR4.Value = "dd"
        rngCogsR5.Value = "ee"
        rngCogsR6.Value = "ff"
        rngCogsR7.Value = "gg"
        
    'Set Cogs Values
        rngCogsR1.Offset(0, 2).Value = 18.75
        rngCogsR2.Offset(0, 2).Value = 0
        rngCogsR3.Offset(0, 2).Value = 0.35
        rngCogsR4.Offset(0, 2).Value = 0.45
        rngCogsR5.Offset(0, 2).Value = 0.5
        rngCogsR6.Offset(0, 5).Value = 0.03
        rngCogsR7.Offset(0, 5).Value = 0.015
        
    'Set Cogs formulas -- zzzz
        'Total
            rngCogsR6.Offset(0, 3).Formula = "=$P$26*$R31"
            rngCogsR7.Offset(0, 3).Formula = "=$P$26*$R32"
            
        'CPM
            Debug.Print rngCogsR6.Offset(0, 2).Address
            rngCogsR6.Offset(0, 2).Formula2 = "=$0$26*$R31"  '<<-- Error here
            rngCogsR7.Offset(0, 2).Formula2 = "=$0$26*$R32"

    'Cell colors
        rngCogsR2.Offset(0, 2).Interior.Color = RGB(255, 255, 204)
        
    'Font colors
        rngCogsR6.Offset(0, 4).Font.Color = RGB(245, 245, 245)
        rngCogsR7.Offset(0, 4).Font.Color = RGB(245, 245, 245)
        
    'Inventory calculation messages
        'Text
            rngCogsR6.Offset(0, 4).Value = "Calculated at 3% x Inventory"
            rngCogsR7.Offset(0, 4).Value = "Calculated at 1.5% x Inventory"
            
        'Text color
            rngCogsR6.Offset(0, 4).Font.Color = vbBlack
            rngCogsR7.Offset(0, 4).Font.Color = vbBlack
            
        'Text italics
            rngCogsR6.Offset(0, 4).Font.Italic = True
            rngCogsR7.Offset(0, 4).Font.Italic = True
            
        'Text size
            rngCogsR6.Offset(0, 4).Font.Size = 9
            rngCogsR7.Offset(0, 4).Font.Size = 9
            
        'Vertical alignment
            rngCogsR6.Offset(0, 4).VerticalAlignment = xlCenter
            rngCogsR7.Offset(0, 4).VerticalAlignment = xlCenter

    'User messages
        MsgDataFee
        
    'Tidy up
        'Worksheet protection
            'If the worksheet is unprotected, protect it, else do nothing
                bProtectionStatus = GetProtectionStatus(ws:=ws)
                    If Not (bProtectionStatus) Then
                        ws.Protect Password:="stuff"
                    End If
                    
        'Destroy objects
            Set rngCogsR1 = Nothing
            Set rngCogsR2 = Nothing
            Set rngCogsR3 = Nothing
            Set rngCogsR4 = Nothing
            Set rngCogsR5 = Nothing
            Set rngCogsR6 = Nothing
            Set rngCogsR7 = Nothing
            Set rngCogsR8 = Nothing
            Set rngCogsR9 = Nothing
            Set rngCogsR10 = Nothing
            Set ws = Nothing
            Set wb = Nothing
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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