SOLVED

VBA language macro

Copper Contributor

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

12 Replies

@Octavio_RamosMy_usern 

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?

best response confirmed by Octavio_RamosMy_usern (Copper Contributor)
Solution

@Hans Vogelaar 

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

@Octavio_RamosMy_usern 

Which cell or cells should contain the formula?

(Why did you mark your own reply as the answer?)

@Hans Vogelaar 

Dear HansIf you allow me I can send you the Excel sheet where I am working

 

@Octavio_RamosMy_usern 

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.

@Hans Vogelaar 

Yes, of course.

I am trying to send the workbook by WeTransfer, but I need a valid email address. Can you provide me one?

@Octavio_RamosMy_usern 

hans dot vogelaar at gmail dot com

@Hans Vogelaar 

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

 

@Octavio_RamosMy_usern 

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.

@Octavio_RamosMy_usern 

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

@Hans Vogelaar 

 

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

 

1 best response

Accepted Solutions
best response confirmed by Octavio_RamosMy_usern (Copper Contributor)
Solution

@Hans Vogelaar 

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

View solution in original post