Announcement

Collapse
No announcement yet.

Compile Error Expected Sub, Function, or Property

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Compile Error Expected Sub, Function, or Property



    I am relatively new to coding in VBA and keep getting the compile error and cannot figure out why it keeps coming up. Any help on quelling the pop up would be helpful.

    Here is the code where I keep getting the error:

    Code:
    Sub tax()
    Tester = 0
    livePriceTotal = Cells(lastLiveRow + 1, 2)
    liveTaxTotal = Cells(lastLiveRow + 1, 3)
    silentPriceTotal = Cells(lastSilentRow + 1, 7)
    silentTaxTotal = Cells(lastSilentRow + 1, 8)
    taxRate = Range("G1").Value
    taxExempt = Range("G2")
    taxRateAdj = ((liveTaxTotal + silentTaxTotal) * (100)) / (livePriceTotal + silentPriceTotal - taxExempt)
    Do
    
    
    If liveTaxTotal + silentTaxTotal = (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 1
        'Cells(1, 8) = taxRateAdj
    If liveTaxTotal + silentTaxTotal <= (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 2
           'ActiveCell(1, 8) = taxRateAdj
    If liveTaxTotal + silentTaxTotal > (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 3
    If Tester = 1 Then taxRate
    If Tester = 2 Then taxRate
    If Tester = 3 Then taxRateAdj
    End Sub

  • #2
    Re: Compile Error Expected Sub, Function, or Property

    You have a "Do" statement but no concluding Loop. Either conclude the loop or remove the Do.

    Comment


    • #3
      Re: Compile Error Expected Sub, Function, or Property

      Removed the "Do" statement.
      Still getting the same error this time the highlighted term is "taxRate"
      Did I not declare taxRate correctly?

      Comment


      • #4
        Re: Compile Error Expected Sub, Function, or Property

        Hello vtflee,

        Welcome to Ozgrid.

        You should never code without using Option Explicit. It forces you to properly declare your variables.
        Turn on Option Explicit while in the VB Editor: Menu > Tools > Options > check the box for Require Variable Declaration

        Unless you haven't posted all of your code, none of your variable are declared.
        I don't see where you have defined "lastliverow" or "lastSilentrow".

        Correct your variable delcarations, then your loop structure, then step through the code.

        Correct/Efficient Uses of Excel Loops
        AAE
        ----------------------------------------------------

        Forum Rules | Message to Cross Posters | How to use Tags

        Comment


        • #5
          Re: Compile Error Expected Sub, Function, or Property

          Thanks.

          I did declare it in the beginning but did not include it in my previous code. I added it right after the sub tax and ran it again, but still getting the same error and it stops at taxRate again.

          Here's the code again:

          Code:
          Sub tax()
          Dim livePriceTotal As Currency
          Dim liveTaxTotal As Currency
          Dim silentPriceTotal As Currency
          Dim silentTaxTotal As Currency
          Dim taxRate As Long
          Dim taxCorrect As Long
          Dim taxExempt As Currency
          Dim taxRateAdj As Long
          
          
          Tester = 0
          livePriceTotal = Cells(lastLiveRow + 1, 2)
          liveTaxTotal = Cells(lastLiveRow + 1, 3)
          silentPriceTotal = Cells(lastSilentRow + 1, 7)
          silentTaxTotal = Cells(lastSilentRow + 1, 8)
          taxRate = Range("G1").Value
          taxExempt = Range("G2")
          taxRateAdj = ((liveTaxTotal + silentTaxTotal) * (100)) / (livePriceTotal + silentPriceTotal - taxExempt)
          
          
          If liveTaxTotal + silentTaxTotal = (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 1
              'Cells(1, 8) = taxRateAdj
          If liveTaxTotal + silentTaxTotal <= (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 2
                 'ActiveCell(1, 8) = taxRateAdj
          If liveTaxTotal + silentTaxTotal > (livePriceTotal + silentPriceTotal - taxExempt) * taxRate Then Tester = 3
          If Tester = 1 Then taxRate
          If Tester = 2 Then taxRate
          If Tester = 3 Then taxRateAdj
          End Sub

          Comment


          • #6
            Re: Compile Error Expected Sub, Function, or Property

            Where do you define lastLiveRow? What is in G1?

            Comment


            • #7
              Re: Compile Error Expected Sub, Function, or Property

              G1 is a tax rate. That value is being pulled from another spreadsheet. I used a vlookup function in the G1 cell.

              Comment


              • #8
                Re: Compile Error Expected Sub, Function, or Property

                Post a workbook.

                Comment


                • #9
                  Re: Compile Error Expected Sub, Function, or Property

                  The other workbook where the tax rates are being pulled is too big to post. Hope that's not an issue.
                  Attached Files

                  Comment


                  • #10
                    Re: Compile Error Expected Sub, Function, or Property

                    Hard to tell but if you declare something as Long it covers integers so if you have something 0.08 you need to declare as Double. Somewhat mystified by the error message you were getting though. I get an overflow error on the taxrateadj line.

                    Comment


                    • #11
                      Re: Compile Error Expected Sub, Function, or Property

                      Yes. Another one of the many problems I am having. I changed taxRate to double, but still getting the same error. Right now I am only debugging the sub tax section. Would I have to fix the overflow problem first?

                      Comment


                      • #12


                        Re: Compile Error Expected Sub, Function, or Property

                        I don't understand why you are getting that error, that is not related to overflow afaik (that is because division by zero, but that may only be because of the lack of links when I ran it). Are you running exactly the same code? Have you stepped through the code using F8?

                        Comment

                        Working...
                        X