Compile error: Invalid use of property - VBA code error

D

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
I hope somebody is able to help with this VBA Code. When I try to run the macro it goes a far as where I have the word formula highlighted in red. This is where I run into a COMPILE ERROR: Invalid use of property. Any help is appreciated.

Rich (BB code): 
Sub Monthly_NOs_Update()
'
' Monthly_NOs_Update Macro
' Column F-ColumnD Column G-ColumnC
'
' Keyboard Shortcut: Ctrl+m
   If Target.Column = 1 Then
        If Target.Value = "Yes" Then
            With Target
                .Font.Name = "Webdings"
                .Value = "a"
            End With
        End If
        If Target.Value = "No" Then
            With Target
                .Font.Name = "Calibri"
                .Value = "x"
            End With
        End If
    End If
    Columns("C:G").Select
    Selection.Copy
    Range("j9:n9").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
   Range("f9").Select
   ActiveCell.Formula "f9" = "=IF(OR(K9=0,m9=0,m9=""misc.""),m9,m9-k9)"
    Range("G9").Select
    ActiveCell.Formula "g9" = "=IF(OR(j9=0,n9=0),n9,n9-j9)"
    Range("F9:f17").Select
    Selection.Copy
    Range("F19:F23").Select
    ActiveSheet.Paste
    Range("F25:F30").Select
    ActiveSheet.Paste
    Range("F32:F36").Select
    ActiveSheet.Paste
    Range("F37:F39").Select
    ActiveSheet.Paste
    Range("F40:F42").Select
    ActiveSheet.Paste
    Range("F43:F45").Select
    ActiveSheet.Paste
    Range("F46:F48").Select
    ActiveSheet.Paste
    Range("f49:f51").Select
    ActiveSheet.Paste
    Range("f53:f64").Select
    ActiveSheet.Paste
    Columns("F:G").Select
    Range("j10:n10").Select
    Selection.Copy
    Columns ("f:G")
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Columns("j:n").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
End Sub
 
Sort by date Sort by votes
Here is what I have thus far for the sheet entry, so long as the values y, n, ok (in any case) are in column A then they will change and colour as desired.

This needs to go in the sheet module for the sheet you are wishing it to work on, so right click that sheets tab and paste this in. I have included commentary that you can edit or delete as you see fit.

Code: 
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


'****************************************************************************
'* As this is likely to result in a infinite loop we want to turn off Events*
'****************************************************************************


Application.EnableEvents = False


'***************************************************************************
'* Event code is only to be triggered on the change of a value in column A *
'***************************************************************************


If Not Intersect(Target, Range("A:A")) Is Nothing Then


'*****************************************************************************
'* People and their need to try and enter values in multiple lines at once!!!*
'*****************************************************************************


If Target.Count > 1 Then
    With Application
        .Undo
        .EnableEvents = True
    End With
    Exit Sub
End If


'********************************************************************************
'* Lets decide what value has been put in column A, is it Y, N, or OK.          *
'* But of course people will use a mix of upper or lower case depending on      *
'* the prevailing weather, so let's just change whatever to lower with LCase    *
'********************************************************************************
    
    Select Case LCase(Target.Value)
        Case Is = "y"
            With Target
                ' green check mark, columns C&D of same row cleared
                .Value = "a"
                .Font.Name = "Webdings"
                .Font.Color = vbGreen
                .Offset(0, 2).Resize(1, 2).Clear    'Will delete values from C&D on same row
                
            End With
        Case Is = "n"
            With Target
                ' red cross mark, columns C&D of same row cleared
                .Value = "r"
                .Font.Name = "Webdings"
                .Font.Color = vbRed
                .Offset(0, 2).Resize(1, 2).Clear    'Will delete values from C&D on same row
            End With
        Case Is = "ok"
            With Target
                ' blue smiley face, columns C&D of same row cleared
                .Value = Chr(74)
                .Font.Name = "Wingdings"
                .Font.Color = vbBlue
                .Offset(0, 2).Resize(1, 2).Clear    'Will delete values from C&D on same row
            End With
        Case Else
        
    End Select
End If


'**************************************************
'* We turned off the events so let's reverse that *
'**************************************************


Application.EnableEvents = True
End Sub
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You also can't copy entire columns and paste to a cell in another range if it's greater than 1
Code: 
    Columns("C:G").Copy
    Range("j9:n9").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats
 
Upvote 0
And for the inserting or formulas I got to this, :confused:

Code: 
Sub Monthly_NOs_Update()
Dim lstRw As Long


lstRw = Range("C" & Rows.Count).End(xlUp).Row


    Range("J9:N" & lstRw).Value = Range("C9:G" & lstRw).Value
    
    Range("F9:F" & lstRw).Formula = "=IF(OR(K9=0,m9=0,m9=""misc.""),m9,m9-k9)"
    Range("G9:G" & lstRw).Formula = "=IF(OR(j9=0,n9=0),n9,n9-j9)"
    Range("F9:G" & lstRw).Value = Range("F9:G" & lstRw).Value
    
End Sub
 
Upvote 0
Hello,

Thanks for helping me on the coding but I still can't get it to run.

I got an error on the line highlighted in red. Runtime error '424' Object required.

I've added a link to my file to perhaps make this easier.

https://app.box.com/s/h0jjjsnsupem4q4vttg4c8nn4mabidn5

Rich (BB code): 
Sub Monthly_NOs_Update()
'
' Monthly_NOs_Update Macro
' Column F-ColumnD Column G-ColumnC
'
' Keyboard Shortcut: Ctrl+m
'****************************************************************************
'* As this is likely to result in a infinite loop we want to turn off Events*
'****************************************************************************




Application.EnableEvents = False




'***************************************************************************
'* Event code is only to be triggered on the change of a value in column A *
'***************************************************************************




If Not Intersect(Target, Range("I:I")) Is Nothing Then




'*****************************************************************************
'* People and their need to try and enter values in multiple lines at once!!!*
'*****************************************************************************




If Target.Count > 1 Then
    With Application
        .Undo
        .EnableEvents = True
    End With
    Exit Sub
End If




'********************************************************************************
'* Lets decide what value has been put in column A, is it Y, N, or OK.          *
'* But of course people will use a mix of upper or lower case depending on      *
'* the prevailing weather, so let's just change whatever to lower with LCase    *
'********************************************************************************
    
    Select Case LCase(Target.Value)
        Case Is = "y"
            With Target
                ' green check mark, columns C&D of same row cleared
                .Value = "a"
                .Font.Name = "Webdings"
                .Font.Color = vbGreen
                .Offset(0, 2).Resize(1, 2).Clear    'Will delete values from C&D on same row
                
            End With
        Case Is = "n"
            With Target
                ' red cross mark, columns C&D of same row cleared
                .Value = "r"
                .Font.Name = "Webdings"
                .Font.Color = vbRed
                .Offset(0, 2).Resize(1, 2).Clear    'Will delete values from C&D on same row
            End With
        Case Is = "ok"
            With Target
                ' blue smiley face, columns C&D of same row cleared
                .Value = Chr(74)
                .Font.Name = "Wingdings"
                .Font.Color = vbBlue
                .Offset(0, 2).Resize(1, 2).Clear    'Will delete values from C&D on same row
            End With
        Case Else
        
    End Select
End If




'**************************************************
'* We turned off the events so let's reverse that *
'**************************************************




Application.EnableEvents = True
    Columns("C:G").Select
    Selection.Copy
    Range("j9:n9").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
   Range("f9").Formula = "=IF(OR(K9=0,m9=0,m9=""misc.""),m9,m9-k9)"
Range("G9").Formula = "=IF(OR(j9=0,n9=0),n9,n9-j9)"
    Range("F9:G9").Select
    Selection.Copy
    Range("F10:F17").Select
    ActiveSheet.Paste
    Range("F19:F23").Select
    ActiveSheet.Paste
    Range("F25:F30").Select
    ActiveSheet.Paste
    Range("F32:F36").Select
    ActiveSheet.Paste
    Range("F37:F39").Select
    ActiveSheet.Paste
    Range("F40:F42").Select
    ActiveSheet.Paste
    Range("F43:F45").Select
    ActiveSheet.Paste
    Range("F46:F48").Select
    ActiveSheet.Paste
    Range("f49:f51").Select
    ActiveSheet.Paste
    Range("f53:f64").Select
    ActiveSheet.Paste
    Columns("f:G").Copy
    ActiveSheet.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Columns("j:n").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In Range("c9:d64")
    With rng
        If .Value > 0 Then .ClearContents
        If .Offset(, x).Value = 0 Then .Offset(, x).ClearContents
    End With
Next rng
Application.ScreenUpdating = True
    fName = ActiveWorkbook.FullName
    fName = Left(fName, InStrRev(fName, ".") - 1) & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
   Application.ScreenUpdating = False
    Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    'insert first sheet name in next line
    If Not ws.Name = "Sheet1" Then ws.PrintOut
Next ws
End Sub
 
Upvote 0
What is "Target"? You haven't defined it (see post #6 from yesterday).

Target is only pre-defined in Event Procedures (basically, it is the cell that is involved in triggering the VBA code).
In your standalone macro, you need to explicitly define it (that is tell the macro exactly what range Target is).
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Macro Formatting to all WS
Replies
4
Views
240
MARK858
MARK858
C
  • Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
379
myall_blues
myall_blues
T
  • Question
ActiveSheet. Paste error
Replies
1
Views
330
RoryA
RoryA
J
  • Question
VBA removing heading in spreadsheet
Replies
3
Views
219
rlv01
R
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
120
willow1985
willow1985

Forum statistics

Threads
1,221,904
Messages
6,162,745
Members
451,785
Latest member
DanielCorn

Share this page

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