VBA: Compile error: Expected end of statement

[marco84]

Hi,

I'd like to enter a Formula into a specific Cell:
Cells(4, 34).Value = "=IF(G4="no data",0,AG4*AE4*-1)"

I can't proceed and get the compile error, the "no data" seems to be the problem, but I need it for the if-statement.

Any idea how to solve that?

Thanks
marco

 

[Peter_SSs]

Maybe this?
Cells(4, 34).Formula = "=IF(G4=""no data"",0,AG4*AE4*-1)"

 

[marco84]

perfect.

Thanks a lot
marco

 

[Peter_SSs]

perfect.

Thanks a lot
marco

No problem.

By the way, Welcome to the MrExcel board!

 

[VBA Newbe]

Hi there,

I'm new to MrExcel and I have a similar challenge!

I have written the following code and receive a 'Compile error: Expected end of statement' message too...

Range("L22").Select
If ActiveCell.Formula = "True" Then
ActiveSheet.Outline.ShowLevels RowLevels:=1

Else ActiveCell.Formula = "False" Then
ActiveSheet.Outline.ShowLevels RowLevels:=2
End If

End Sub

Any assistance would be greatly appreciated.
Cheers

 

[Ragnar1211]

"Else" does not require a "Then" statement. Else means - if you get here do this. Remove the "ActiveCell.Formula = "False" Then" and you should be good.

 

[siddharth2008]

Hi,

I am trying to build a formula in VBA bu getting the same error on the following line:

rng3.Formula="=IF(AY"&j&"=""Failed"",1,0)"

Here, rng3 is a defined range, and j is an integer to pick row number.

Can you please help.

 

[RoryA]

You are missing spaces:

   rng3.Formula = "=IF(AY" & j & "=""Failed"",1,0)"

 

[siddharth2008]

You are missing spaces:

   rng3.Formula = "=IF(AY" & j & "=""Failed"",1,0)"

That was a pretty silly mistake.

Thanks.

 

[Kamusksn]

I'm trying to set a formula in a spesific field

'This code insert the formula
Dim Formula As String
Formula = _
"=COUNTA(F2:F25000)"
Range("R2").Formula = Formula

'This code not inset the formula [Runtime Error 1404 "Application-Deffined or Objet-Deffined error]
Dim Formula As String
Formula = "=IF(AND(C2="",A2<>""CC"",A2<>""PF"",A2<>""PU"",A2<>""40""),16,A2)"
Range("P2").Formula = Formula

any know why?
Thanks.