CODEWITHSUNDEEP
vbaexcel
RobK
RobK

Reputation: 207

Add formula to cell error

I've got this problem and it's driving me crazy. Probably easy to fix, but I don't see what I'm doing wrong.

I have this code to create a new worksheet based on another worksheet in a workbook.

Copying the worksheet is not a problem, but I also need to add this sheet into an overview sheet, where I use a formula to see whether the sheet is active or not.

This is the code:

'Create new worksheet
Sheets("Leeway").Copy after:=Sheets("Leeway")
Set WS = ActiveSheet
WS.Name = "Leeway " & ddCountries.Value

'Add new worksheet to Control sheet
Dim c As Range
Set c = Sheets("Control Sheet").Range("H5")
Do Until c.Value = ""
    Set c = c.Offset(1, 0)
Loop

c.Value = WS.Name
c.Offset(0, 1).Formula = "=IF('" & WS.Name & "'!L42<>"""";""Active"";""Inactive"")"

At the last line, where the formula is added gives the following error: "1004: Application-defined or object-defined error".

I've added this formula to the watch and (I'm pretty sure that) it gives the correct formula.

What am I doing wrong here?

Upvotes: 1

Views: 59

Answers (1)

DragonSamu
DragonSamu

Reputation: 1163

in VBA code you have to use , instead of ; for a .Formula to be put into a cell

Upvotes: 4

Related Questions