Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Compile Error Expected Sub, Function, or Property

  1. #1
    Join Date
    9th February 2012
    Posts
    11

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,404

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th February 2012
    Posts
    11

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,205

    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

  5. #5
    Join Date
    9th February 2012
    Posts
    11

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,404

    Re: Compile Error Expected Sub, Function, or Property

    Where do you define lastLiveRow? What is in G1?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th February 2012
    Posts
    11

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,404

    Re: Compile Error Expected Sub, Function, or Property

    Post a workbook.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    9th February 2012
    Posts
    11

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,404

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: December 19th, 2006, 03:53
  2. Compile error: Sub or function not defined
    By samuels90 in forum EXCEL HELP
    Replies: 5
    Last Post: July 29th, 2006, 14:35
  3. Compile Error Expected Function or Variable
    By Niggle in forum EXCEL HELP
    Replies: 2
    Last Post: July 27th, 2006, 18:37
  4. Compile error expected =
    By Canuck in forum EXCEL HELP
    Replies: 1
    Last Post: November 17th, 2005, 10:30
  5. Compile error : Sub or Function not defined
    By alienscript in forum Excel and/or Access Help
    Replies: 5
    Last Post: October 15th, 2004, 05:21

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno