How to hardcode a concatenate formula into a cell

Hi, I'm an experienced Excel user, but only getting into the VBA side of things. I want to automate some processes in an old spreadsheet to save time and control data entry. However, I'm stumbling on how to add the concatenate function code. The spreadsheet has rows 1:8 as the "header", so beginning on row 9, in column A, I have the following formula:

=IF(B9="","",CONCATENATE($A$1," - ",$A$2)) 'if B9 is blank, then leave A9 blank; otherwise, put the "year - month" in A9. Column B is the person's name.

In my protected spreadsheet, I have prefilled A9:A100 with the above formula because the users tend to either forget to put in the year and month or they use different date formats. At any given time, not all of these rows will have data; thus, the reason for wanting to leave column A blank if there is no name in column B.

If the user needs to insert a row, then I've created a macro button that will unprotect, insert the row, fill certain cells with predefined data, and protect; however, I can't get the macro to hardcode the concatenate formula into column A. I keep getting various error messages. Obviously, my syntax is wrong.

Can someone please show me how to do this?

Thanks!

Originally Posted by Murman01

however, I can't get the macro to hardcode the concatenate formula into column A. I keep getting various error messages. Obviously, my syntax is wrong.

Can someone please show me how to do this?

Which error messages?
Which line(s) of your code to they relate to?
What does your code look like?
Can we see the workbook and the code? Even if in a desensitized version?
Is it nearly bed time?

Oh so many questions!

BSB

Thanks everyone for your help! It was the extra double set of quote that was catching me up (now I need to go and research WHY those quotes are necessary!).

My code that now works as desired is:
.Range("A" & ActiveCell.Row).Formula = "=If(B" & ActiveCell.Row & "="""","""",CONCATENATE($A$1,"" - "",$A$2))"

Thanks Skywriter!