Compile error: Invalid use of property - VBA code error

[dstepan]

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.

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

 

[Joe4]

What is the purpose of that "f9" in there for? It shouldn't be there.
You can also combine the two rows toegether (you seldom need a ".Select" followed by a "Selection.").
So all of this:

Range("f9").Select
   ActiveCell.[COLOR=#ff0000]Formula[/COLOR] "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)"

can be combined like this:

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)"

 

[dave3009]

Hi

Remove the "f9" and "g9" and your code should work OK.

HTH

Dave

 

[dstepan]

Thanks Joe & Dave - I'm not sure it works because now I'm stuck the first part of the code.
I have column "I" which a person will enter a {y, n, ok}.
However, I want these to be changed via formatting. I want the y = green smiley face. I want n = red x. And I want ok = blue checkmark.
I used 3 types of formatting as listed - see very beginning of code below. However, when I try to run the code now I get a Run-time error '424': Object required. I can't remember now if it was the 1st or 2nd line of the code see green highlights. Also, when it stepped on I came to another error (see purple highlight). The error was "Compile error: Invalid use of property. The orange highligt is this error "Runtime error 1004: Application define or Object defined error"
Any ideas?

Sub Monthly_NOs_Update()
'
' Monthly_NOs_Update Macro
' Column F-ColumnD Column G-ColumnC
'
' Keyboard Shortcut: Ctrl+m
   If Target.Column = 9 Then
        If Target.Value = "y" Then
            With Target
                .Font.Name = "Carta"
                .Value = "J"
            End With
        End If
        If Target.Value = "n" Then
            With Target
                .Font.Name = "Bookshelf Symbol 7"
                .Value = "i"
            End With
        End If
        If Target.Value = "ok" Then
            With Target
                .Font.Name = "ZDingbats"
                .Value = "4"
            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").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").Select
    Range("j10:n10").Select
    Selection.Copy
    Columns("F:G").Select
    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

 

[dave3009]

I'm sure Joe4 will correct me if I'm wrong, but it looks like you're running this as a standalone macro...Target would need to be declared as something, it would work OK if you were running from a worksheet event.

The next bit might be down to you selecting the entire column and then pasting into row 9, that size of the copied range wouldn't fit.

If you could put in words what it's to do I'll try and tidy it up for you

 

[Joe4]

I'm sure Joe4 will correct me if I'm wrong, but it looks like you're running this as a standalone macro...Target would need to be declared as something, it would work OK if you were running from a worksheet event.

Good catch. I missed that.

"Target" is used in Event Procedures. I suppose you could use it as a variable in standalone procedure, though you would need to define it, which you haven't done (so "Target" actually is nothing in your procedure).

 

[dstepan]

I am trying to Copy & Paste as Value Columns C:G to Columns J:N.
I then want to put a formula in f9 and g9. (This you have already helped me on).
Then I want to copy the formulas in f9 and g9 down through the various groups of rows. Ultimate end is for all rows in columns C:D are cleared for next months data input.
Once this is done, I want to copy & paste as value C:G and Delete J:N.
I would like for the clerk to be able to enter "y", "n", "ok" and have the code change the formatting to reflect a green "smiley face",a red "X" and a blue "checkmark".
Then I would like to clear contents for any data entered into the same various group of rows but only in Columns C & D.
Finally, I would like the file to be saved.
Thanking you all in advance for any help!

 

[dstepan]

Can you all explain what you mean by a "standalone macro" (vs. what) and Event procedures and variable in a standalone macro. I'm trying very hard to learn VBA on my own so I needn't bother good folks like you all. I would like to be one of the people helping others. Someday perhaps.

 

[Joe4]

Event Procedure code is a "special" kind of VBA code. It is VBA that is automatically triggered upon some "event" happening, like opening the file, changing a cell, selecting a cell, saving a file, etc. In order for it to work, it needs to follow strict rules, specifically it has to be located in the proper Workbook or Worksheet module and must be named a certain way (there is no flexibility in that). See here for more details on it: Events In Excel VBA

Then there are procedures that you create yourself and put in standard modules. These can be named most anything you like (following some basic naming convention rules). These do not run automatically. Usually, your assign these to a keyboard shortcut, a command button, or simply run them through the Macro menu. The main point being, is you need to explicitly do something in order to run these macros. User Defined Functions also fall into this category.

 

[dave3009]

Hi dstepan

Am I to take it there will be three happenings here?

1. You set up the spreadie.
2. Your clerk enters the y, n, ok (deleting the values from columns C&D on that row)
3. You finalise the spreadie

Is that the general run of it?

Thanks

Dave