Syntax error in vba excel code [duplicate]

Question

I am adding a button in my excel sheet for adding an employee with above used formulas.

First two formulas in the below code work perfect but last one giving syntax error.

My code is :

Private Sub AddEmployee_Click()

    Sheets("Sheet1").Range("A35").Select
    ActiveCell.EntireRow.Insert shift:=xlDown

    Sheets("Sheet1").Range("A35:AJ35").Select
    Selection.Borders.Weight = xlThin

    Sheets("Sheet1").Range("AJ35").Select
    ActiveCell.Formula = "=SpeDays(C35:AG35,9)"

    Sheets("Sheet1").Range("AI35").Select
    ActiveCell.Formula = "=IF(AI35>=10,10,AI35)"

    Sheets("Sheet1").Range("AH35").Select
    ActiveCell.Formula = "=COUNTIF(C35:AG35,">=4000")"

On a side note: you can remove the select and activecell and join the two lines - Sheets("Sheet1").Range("A35").EntireRow.Insert shift:=xlDown — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jul 24, 2017 at 8:07

diiN__________ · Accepted Answer · 2017-07-24 08:01:24Z

3

The problem are your quotes inside the formula. Use double quotes as a solution:

ActiveCell.Formula = "=COUNTIF(C35:AG35, "">=4000"")"

answered Jul 24, 2017 at 8:01

diiN__________

7,7197 gold badges49 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pratyush Kumar Over a year ago

but excel file return formula like this : =IF(AI36>=10,10,AH36)

Pratyush Kumar Over a year ago

it is showing application defined or object defined error

Pratyush Kumar Over a year ago

IT works brother. I have made some mistakes, now identified. thanks

Mrig · Accepted Answer · 2017-07-24 09:20:00Z

1

Use can use the below code as well

Activecell.Formula = "=Countif(C35:AG35," & chr(34) & ">=4000" & chr(34) & ")"

Output will be:

=Countif(C35:AG35,">=4000")

edited Jul 24, 2017 at 9:20

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

answered Jul 24, 2017 at 8:14

nishit dey

4581 gold badge7 silver badges22 bronze badges

5 Comments

Pratyush Kumar Over a year ago

it is showing application defined or object defined error

Mrig Over a year ago

@PratyushKumar - There's typo, changeAG3,5 to AG35,

Pratyush Kumar Over a year ago

Is there any way to apply the same code for last row determined automatically in place of A35 OR AJ35....

nishit dey Over a year ago

LastRow = ActiveSheet.Cells(.Rows.Count, "A").End(xlUp).Row + 1 For this, you need to have a fixed row, either its in A coloumn or AJ column. Thanks

Pratyush Kumar Over a year ago

@nishit dey- for this i have to put only $ with A or AJ. am i right

Collectives™ on Stack Overflow

Syntax error in vba excel code [duplicate]

2 Answers 2

3 Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Linked

Related