+ Reply to Thread
Results 1 to 6 of 6

Compile Error: Expected expression

Hybrid View

drewship Compile Error: Expected... 01-14-2016, 12:30 PM
AB33 Re: Compile Error: Expected... 01-14-2016, 01:22 PM
drewship Re: Compile Error: Expected... 01-14-2016, 01:41 PM
AB33 Re: Compile Error: Expected... 01-14-2016, 01:55 PM
drewship Re: Compile Error: Expected... 01-14-2016, 02:33 PM
drewship Re: Compile Error: Expected... 01-14-2016, 02:27 PM
  1. #1
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Compile Error: Expected expression

    Hello all.
    Trying to compare values between cells in row 1 with offset cells in row 3 and get a 'Compile Error: Expected expression' on the colored line. I tried various combinations of () with various errors. How should this be written? Thanks.


    Dim All_Data As Range
    Dim Response As Range
    Dim MyCell As Range, cellOffset As Range
    Dim myRange As Range
    Dim rng As Range
    
    Set All_Data = Range("D1:F1")
    Set cellOffset = All_Data.Offset(2, )
    
    For Each MyCell In All_Data
    If MyCell.Value < cellOffset.Value Then
    'If MyCell.Value < 10 Then
       MyCell.Interior.ColorIndex = 3 'RED

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Compile Error: Expected expression

    Option Explicit
    Sub test()
    
    
    Dim All_Data As Range
    Dim Response As Range
    Dim MyCell As Range, cellOffset As Range
    Dim myRange As Range
    Dim rng As Range
    
    Set All_Data = Range("D1:F1")
    Set cellOffset = All_Data.Offset(2)
    
       For Each MyCell In All_Data
            If MyCell.Value < cellOffset.Value Then
            'If MyCell.Value < 10 Then
               MyCell.Interior.ColorIndex = 3 'RED
           End If
       Next
    End Sub

  3. #3
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Compile Error: Expected expression

    Thanks AB33. I copied the code block but now I get Run-Time error 13: Type mismatch on the colored line below. Both MyCell and cellOffset are ranges so I am confused. If cell "D1" is being compared to cell "D3", the offset(2) should do this. The only thing I can think of is that "D1:F1" calculated values based on a "CountIf" formula. Wouldn't .Value get the actual value of the cell?

    Sub Formatter()
    
    Dim All_Data As Range
    Dim Response As Range
    Dim MyCell As Range, cellOffset As Range
    Dim myRange As Range
    Dim rng As Range
    
    Set All_Data = Range("D1:F1")
    Set cellOffset = All_Data.Offset(2)
    
       For Each MyCell In All_Data
            If MyCell.Value < cellOffset.Value Then
               MyCell.Interior.ColorIndex = 3 'RED
       Else
            If MyCell.Value > cellOffset.Value Then
            MyCell.Interior.ColorIndex = 6 'YELLOW
            Else
                If MyCell.Value = cellOffset.Value Then
                MyCell.Interior.ColorIndex = 4 'GREEN
                End If
            End If
    End If
    Next
    
    'refreshes the function results after the main code is complete
        Set myRange = ActiveSheet.Range("A1:F1")
    
        For Each rng In myRange
            rng.Formula = rng.Formula
        Next
    
    End Sub
    Last edited by drewship; 01-14-2016 at 01:55 PM.

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Compile Error: Expected expression

    I knew the code was wrong.

    Set cellOffset = All_Data.Offset(2

    is a range, so you can not compare(Evaluate) it against a range, but you can against a single cell

    If MyCell.Value < cellOffset.Value Then

    You can use this line
    If MyCell.Value < MyCell.Offset(2) Then
    ' this is going down

    OR

     If MyCell.Value < MyCell.Offset(, 2) Then ' this is going to the right

  5. #5
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Compile Error: Expected expression

    Tried to add rep but won't let me until I spread some more around...

  6. #6
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Compile Error: Expected expression

    PERFECT!!!!!!! I know enough to understand what I am trying to do logically but it is the details that get me every time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] UDF gives compile error: expected:expression
    By adh2 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-10-2015, 05:37 PM
  2. Compiile error: Expected: expression
    By soldgold in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-01-2015, 04:46 AM
  3. VBA error question (Expected expression) Application.Worksheetfunction
    By Gijsbenjezelf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-04-2014, 12:40 PM
  4. [SOLVED] Compile Error: Expected: expression
    By Wheelie686 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2013, 08:51 AM
  5. [SOLVED] Compile Error: Expected Expression, Syntax Error
    By gjohn282 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2012, 11:28 PM
  6. VBA compilation error : expected expression error
    By raknahs in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-21-2010, 12:57 PM
  7. if isna vlookup expected expression error
    By harishankarin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2010, 04:16 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1