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

 

[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.

 

[VoG]

Try

Formula = "=IF(AND(C2="""",A2<>""CC"",A2<>""PF"",A2<>""PU"",A2<>""40""),16,A2)"

 

[Peter_SSs]

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.

Yes, you have doubled up all the "" except the ones I have made red.

BTW, it is a BAD idea to use one of vba's 'special' words as a variable name.
You would be much better of using something like
Dim frmla as String

 

[Sarabjit]

Hi, I am trying to do attendance sheet. I want it to send emails automatically to students with short attendance. Its working perfectly now. Only thing i want to change is its matter. At this point matter is very simple and short for example "


.body = "Hi " & Cells(row, namecol).Value & vbCrLf & vbCrLf & "It has come to our attention that your students attendance is below 90% ." & vbCrLf & vbCrLf & "This is a serious matter as failure to attend current lessons unauthorized can lead to their grades suffering and possible expulsion from the College. If they are currently having any form of problems which is causing problems with their attendance, then please come and see me to discuss it in private." & vbCrLf & vbCrLf & vbCrLf & "Kind regards" & vbCrLf & vbCrLf & "YOUR NAME"



Now I want to change this message to bit longer one. First it is not allowing me to write after reaching end point. It is also not letting me to go to next line. If I press enter then it shows the msg in red. and shows different compile errors. Following is the msg I am trying to enter. Please let me know where I am doing wrong. Thanks






.body = "Dear" & Cells(row, namecol).Value & vbCrLf & vbCrLf & "Please be informed that it has been brought to our attention that you have not attended some classes this study block. as an on shore international student holding a student visa, enrolled in National Diploma in Business Level 6, you are required to comply with a number of condition related to that visa [as per Immigaration Act 2009], including attending at least 85% of your scheduled class hours in a learning block.Your projected attendance for the semester is currently approximately 95%." & vbCrLf & vbCrLf & "Failure to complay with your attendance requirements can lead to the cancellation of your visa and ultimately, your early departure from country. It should also be noted that, under the country law, The Academy must report a student who can no longer achieve 85% attendance to the immigration authorities."
& vbCrLf & vbCrLf & "If you continually miss your scheduled class hours and your attendnce falls below 85% for learning block or paper that you are enrolled in we will be forced to notify your breach of satisfactory attendance to the Immigration . " & vbCrLf & vbCrLf & "It is important to ensure that you attend all classes. If you are sick you may submit a doctor's certificate, however, you will still be recorded as absent." & vbCrLf & vbCrLf & "In order to discuss any difficulties you may be experiencing that are affecting your attendance please contact me on telephone 000000000 or email abc@yahoo.com." & vbCrLf & vbCrLf & "you may also wish to conatct the Manager of the Student Services Deapartment to discuss this matter in confidence. The contact details are:" & vbCrLf & vbCrLf & "Name" & vbCrLf & vbCrLf & " Student Service Manager " & vbCrLf & vbCrLf & " Telephone:000000000" & vbCrLf & vbCrLf & "Email:abc@yahoo.com"


Please help.

Thanks and regards
Sarabjit Singh

 

[nguyentrungkien26386]

Hi,

I have the same question (Compile error: Expected end of statement):

I am trying to set formula for this :

Cells(i + 2, 14) = "=(Minute(R"&i+1&"C6)*R"&i&"C9+R"&i+1&"C10*(R"&i&"C9+R"&i+1&"C9)/2+(Minute(R"&i+2&"C6)-R"&i+1&"C10-Minute(R"&i+1&"C6))*R"&i+1&"C9+(60-Minute(R"&i+2&"C6))*(R"&i+1&"C9+R"&i+2&"C11)/2)/60"

Any know why?

Thanks

 

[Michael M]

@Sarabijt and nguyentrungkien26386
It might be more appropriate if you posted a new thread for your question/s rather than jumping on the end of someone elses