+ Reply to Thread
Results 1 to 6 of 6

If Statement Issue

Hybrid View

  1. #1
    Registered User
    Join Date
    09-21-2015
    Location
    Rochester NY
    MS-Off Ver
    2010
    Posts
    18

    If Statement Issue

    I'm getting a Run-time error '1004': Application-defined or object-defined error. Occuring on the bolded line below.
    That line is simply trying to check .Range(Row, 8) against "y". The Row variable is retaining the correct correct value so I'm confident that the issue isn't with that. I think I am referencing the worksheet incorrectly?.... Thanks in advance for any help or suggestions.

    Originally CStr() was not included, however I am getting the same error before and after the change.


                For Each cell In Worksheets("Change Master Task List").Range("M:M")
                    If CInt(c(i, j)) = cell.Value Then
                        Row = cell.Row
                        If CStr(Worksheets("Change Master Task List").Range(Row, 8)) = "y" Then
                            c(i, j).Interior.ColorIndex = 23
                        End If
                    End If
                Next cell

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,344

    Re: If Statement Issue

    c(i, j) is an array element. Presumably, you should be referring to cell rather than c(i, j)


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: If Statement Issue

    Are you sure there shall be Range, not
    Cells(Row , 8)
    ?
    Best Regards,

    Kaper

  4. #4
    Registered User
    Join Date
    09-21-2015
    Location
    Rochester NY
    MS-Off Ver
    2010
    Posts
    18

    Re: If Statement Issue

    Cells(row, 8) works I believe... A little confused as to why it does

  5. #5
    Registered User
    Join Date
    09-21-2015
    Location
    Rochester NY
    MS-Off Ver
    2010
    Posts
    18

    Re: If Statement Issue

    TMS ~ What is your advice? c(i, j) is working and is a defined range.

    Kaper ~ As you see in the code below, cells(row, 8) would be out of range of the defined range for the For loop;
    For Each cell In Worksheets("Change Master Task List").Range("M:M")
    I'm trying to reference (row, 8) of that whole worksheet, cells(row,8) wouldn't reference outside of the ("M:M") Range correct?

    c = Worksheets("Timeline").Range("B82:BI762")                                
    
    For i = 1 To Worksheets("Timeline").Range("B82:BI762").Rows.Count            
        For j = 1 To Worksheets("Timeline").Range("B82:BI762").Columns.Count
            If IsEmpty(c(i, j)) Then
                
            Else
                For Each cell In Worksheets("Change Master Task List").Range("M:M")
                    If CInt(c(i, j)) = cell.Value Then
                        Row = cell.Row
                        If CStr(Worksheets("Change Master Task List").Range(Row, 8)) = "y" Then
                            c(i, j).Interior.ColorIndex = 23
                        End If
                    End If
                Next cell
            End If
        Next j
    Next i

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: If Statement Issue

    You can stick with Range if you want, but then:
    If CStr(Worksheets("Change Master Task List").Range("H" & Row)) = "y" Then
    Simplifying a bit: in the form you used it was treated by VBA as:
    Range(starting_cell , finishing_cell)
    and of course neither 8 nor Row are good representations of a cell.

+ 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. if statement issue
    By headford in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2013, 12:30 PM
  2. If statement Issue
    By Dinho05 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-20-2012, 06:04 PM
  3. Issue with IF statement
    By DennyMathews in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-13-2012, 05:39 AM
  4. in between #...issue if statement
    By jw01 in forum Excel General
    Replies: 7
    Last Post: 06-29-2011, 11:04 AM
  5. Issue w/o sum, but with if statement
    By jasonkbu in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-20-2008, 03:26 PM
  6. If Statement issue
    By Mike Shamrock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-07-2006, 06:02 PM
  7. If statement issue
    By punter in forum Excel General
    Replies: 3
    Last Post: 11-27-2006, 04:39 PM
  8. [SOLVED] FOR Statement Issue
    By G in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-09-2005, 01:05 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