Apr 05 2023 12:52 PM
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
Apr 05 2023 01:22 PM
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?
Apr 05 2023 01:48 PM
SolutionHi 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
Apr 05 2023 02:25 PM
Which cell or cells should contain the formula?
(Why did you mark your own reply as the answer?)
Apr 05 2023 03:07 PM
Apr 05 2023 03:24 PM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
Apr 05 2023 03:46 PM
Apr 06 2023 03:34 AM
hans dot vogelaar at gmail dot com
Apr 06 2023 11:48 AM
Good afternoon dear Hans,
I just sent a Dropbox message to the Microsoft account, please check your inbox to see if it's received. Thank you so much
Sincerely
Apr 06 2023 12:18 PM
Please check whether you have used the correct email address - I haven't received anything (I checked the Spam folder too).
A private message might be easier.
Apr 06 2023 12:58 PM
I have received your email. Here is a new version of your macro (also sent in reply to your email):
Sub CargoSed()
Dim VarSed As Range
For Each VarSed In Worksheets("Cargo por tratamiento").Range("U4:U24")
If VarSed < 0.1 Then
VarSed = 0
ElseIf VarSed < 0.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*1.71"
ElseIf VarSed < 1 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*2.10"
ElseIf VarSed < 1.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*2.34"
ElseIf VarSed < 2 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*2.52"
ElseIf VarSed < 2.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*2.68"
ElseIf VarSed < 3 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*2.81"
ElseIf VarSed < 3.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*2.92"
ElseIf VarSed < 4 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.03"
ElseIf VarSed < 4.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.11"
ElseIf VarSed < 5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.20"
ElseIf VarSed < 5.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.29"
ElseIf VarSed < 6 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.39"
ElseIf VarSed < 6.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.49"
ElseIf VarSed < 7 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.60"
ElseIf VarSed < 7.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.70"
ElseIf VarSed < 8 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.82"
ElseIf VarSed < 8.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*3.93"
ElseIf VarSed < 9 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*4.05"
ElseIf VarSed < 9.5 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*4.16"
ElseIf VarSed < 10 Then
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*4.292"
Else
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*4.42"
End If
Next VarSed
End Sub
Apr 06 2023 02:44 PM - edited Apr 07 2023 01:59 PM
Some improvements for your consideration (untested)....
Sub CargoSed()
Dim VarSed As Range
Dim x As Double ' NEVER use type Single!
For Each VarSed In Worksheets("Cargo por tratamiento").Range("U4:U24")
If VarSed < 0.1 Then
VarSed = 0
Else
Select Case VarSed.Value2 ' Value2 is more reliable
Case Is < 0.5: x = 1.71
Case Is < 1: x = 2.1
Case Is < 1.5: x = 2.34
Case Is < 2: x = 52
Case Is < 2.5: x = 2.68
Case Is < 3: x = 2.81
Case Is < 3.5: x = 2.92
Case Is < 4: x = 3.03
Case Is < 4.5: x = 3.11
Case Is < 5: x = 3.2
Case Is < 5.5: x = 3.29
Case Is < 6: x = 3.39
Case Is < 6.5: x = 3.49
Case Is < 7: x = 3.6
Case Is < 7.5: x = 3.7
Case Is < 8: x = 3.82
Case Is < 8.5: x 3.93
Case Is < 9: x = 4.05
Case Is < 9.5: x = 4.16
Case Is < 10: x = 4.292
Case Else: x = 4.42
End Select
VarSed.FormulaR1C1 = "=((RC5-R[-1]C5)/1000)*RC2*" & x
End If
Next VarSed
End Sub
Edit.... In hindsight, I see that @Octavio_RamosMy_usern wrote "I am starting to work [...] with the If...Then...Else statement". So maybe he would prefer that construct for educational purposes. But I would still just calculate a variable x in the If...Then...Else construct and append it to the rest of the formula in one statement.
@Octavio_RamosMy_usern: Note some subtleties:
1. Always use type Double, not Single. It makes no difference in this context. But it can make a significant difference if you store the variable directly into an Excel cell. For example:
Dim x As Single
x = 4.292
[a1].Value2 = x
results in 4.29199981689453 in A1, not 4.292.
And the same anomaly can happen in VBA assignments if you mix type Single and Double variables.
2. In general, Value2 is more reliable than Value (which is the default, if omitted). Again, I suspect that it makes no difference in this context. But it can have a significant affect on the VBA interpretation of cell values that are formatted as some form of Currency or Accounting and Date.
This comment is mostly just to raise your awarenesss of the issue. I am not as "religious" about using Value2 as I am about using type Double. Truth be told, I often omit even the Value property, as HansV did. Then I spend hours debugging when something mysterious happens, only to discover that I should have used Value or Value2. Better to be safe than sorry when you are just "starting to work with VBA".
Apr 05 2023 01:48 PM
SolutionHi 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