Forum Discussion
Solved
VBA language macro
Hi Cuong,
I am starting to work with VBA language to generate a macro with the If...Then...Else statement, and I have a problem in a line that contains a formula. I copy my macro and highlight the problem
Option Explicit
'Public CSSed As Single
Sub CargoSed()
Dim VarSed As Single
VarSed = Application.Sheets("Cargo por tratamiento").Range("U4:U24").Value
If VarSed < 0.1 Then
VarSed = 0
ElseIf 0.1 <= VarSed < 0.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*1.71"
ElseIf 0.5 <= VarSed < 1 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*2.10"
ElseIf 1 <= VarSed < 1.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*2.34"
ElseIf 1.5 <= VarSed < 2 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*2.52"
ElseIf 2 <= VarSed < 2.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*2.68"
ElseIf 2.5 <= VarSed < 3 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*2.81"
ElseIf 3 <= VarSed < 3.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*2.92"
ElseIf 3.5 <= VarSed < 4 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.03"
ElseIf 4 <= VarSed < 4.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.11"
ElseIf 4.5 <= VarSed < 5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.20"
ElseIf 5 <= VarSed < 5.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.29"
ElseIf 5.5 <= VarSed < 6 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.39"
ElseIf 6 <= VarSed < 6.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.49"
ElseIf 6.5 <= VarSed < 7 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.60"
ElseIf 7 <= VarSed < 7.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.70"
ElseIf 7.5 <= VarSed < 8 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.82"
ElseIf 8 <= VarSed < 8.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*3.93"
ElseIf 8.5 <= VarSed < 9 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*4.05"
ElseIf 9 <= VarSed < 9.5 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*4.16"
ElseIf 9.5 <= VarSed < 10 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*4.292"
ElseIf VarSed > 10 Then
VarSed.Formula = "=((E4-E3)/1000)*B4*4.42"
End If
End Sub
When I start running the program, and I reach the variable of the indicated line, a message is displayed that says "Compilation error" "Invalid qualifier"
Please can you help me and tell me where my mistake is.
Thank you so much
Octavio Ramos
email address removed for privacy reasons
Hi Hans,
Thank you for answering me, the macro that I am doing is used to determine the monetary charge that must be made to the users of the wastewater treatment plants when their contamination levels are higher than those allowed by the authority.
Once the contamination value of each of the parameters that must be met is known, a non-compliance index is established and, depending on the non-compliance index, it is the charge in money.
The ranges will be for example
0.1<= Index >0.5
0.5 <= Index < 1
Etc
You declare VarSed as a variable of type Single. The line
VarSed.Formula = "=((E4-E3)/1000)*B4*1.71"
expects VarSed to be a variable of type Range, not of type Single. Also, the line
ElseIf 0.1 <= VarSed < 0.5 Then
should be
ElseIf VarSed < 0.5 Then
and similar for the other ElseIf lines except for the last one. The line
VarSed = Application.Sheets("Cargo por tratamiento").Range("U4:U24").Value
won't work either, since the value of a multi-cell range is an array of values.
What exactly do you want the macro to do?
Hi Hans,
Thank you for answering me, the macro that I am doing is used to determine the monetary charge that must be made to the users of the wastewater treatment plants when their contamination levels are higher than those allowed by the authority.
Once the contamination value of each of the parameters that must be met is known, a non-compliance index is established and, depending on the non-compliance index, it is the charge in money.
The ranges will be for example
0.1<= Index >0.5
0.5 <= Index < 1
EtcWhich cell or cells should contain the formula?
(Why did you mark your own reply as the answer?)
