Syntax error in vba excel code

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, Jul 24 '17 at 08:07

score 3 · Answer 1 · answered Jul 24 '17 at 08:01

3

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

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

answered Jul 24 '17 at 08:01

diiN__________

7,393
6
42
69

but excel file return formula like this : =IF(AI36>=10,10,AH36) – Pratyush Kumar Jul 24 '17 at 08:16
it is showing application defined or object defined error – Pratyush Kumar Jul 24 '17 at 08:30
IT works brother. I have made some mistakes, now identified. thanks – Pratyush Kumar Jul 24 '17 at 08:48

score 1 · Answer 2 · edited Jul 24 '17 at 09:20

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 '17 at 09:20

Mrig

11,612
2
13
27

answered Jul 24 '17 at 08:14

nishit dey

458
1
7
21

it is showing application defined or object defined error – Pratyush Kumar Jul 24 '17 at 08:30
@PratyushKumar - There's typo, change`AG3,5` to `AG35,` – Mrig Jul 24 '17 at 09:14
Is there any way to apply the same code for last row determined automatically in place of A35 OR AJ35.... – Pratyush Kumar Jul 24 '17 at 12:21
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 – nishit dey Jul 24 '17 at 12:44
@nishit dey- for this i have to put only $ with A or AJ. am i right – Pratyush Kumar Jul 25 '17 at 06:12

Syntax error in vba excel code

2 Answers2