IF Cell Equals then.....

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: IF Cell Equals then.....

  1. #1
    New Member
    Join Date
    Feb 2003
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF Cell Equals then.....

     
    Hi,

    I'm using date codes on my excel sheet, so 1 being Sunday and 7 being Saturday.

    I want excel to open a particular sheet on a particular day. So basically have it watch for 1 or 7 and then load a sheet.

    I've tried to modify

    =if(E14=1,"Sunday")

    and have it look something like

    =if(E14=1, application.run("personal.xls!open_sheet"))

    Any ideas?

    Neil.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Cell Equals then.....

    Hi,

    It is not possible to do what you wish using a worksheet formula.

    What you can do is use an event macro to accomplish the same thing...

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SettingsArray
    
    With Application
        SettingsArray = Array(.Calculation, .EnableEvents, .ScreenUpdating)
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = True
    End With
    
    With Target
        If .Cells.Count = 1 And .Address(False, False) = "E14" Then
            If .Value = 1 Then
                Application.Run ("personal.xls!open_sheet")
            End If
        End If
    End With
    
    
    With Application
        .Calculation = SettingsArray(0)
        .EnableEvents = SettingsArray(1)
        .ScreenUpdating = SettingsArray(2)
        
    End With
    
    
    End Sub
    Bye,
    Jay

  3. #3
    New Member
    Join Date
    Feb 2003
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Cell Equals then.....

    Cheers for the response.

    Got a problem though.....

    I step into the macro, and all is ok until it gets to:

    Code:
    If .Cells.Count = 1 And .Address(False, False) = "C43" Then
    it then gives:

    Run Time Error: 424. Object Required

    Any ideas on this?

  4. #4
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Cell Equals then.....

    Jay would know this cold, but as he's not around I'll take a guess that you need --

    If .Cells.Count = 1 And .Address(False, False) = .Range("C43") Then
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  5. #5
    New Member
    Join Date
    Feb 2003
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Cell Equals then.....

    Still doesn't work. Still says Object Required.

    Any ideas?

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Cell Equals then.....

    I think you need to omit the Cells property:

    If .Count = 1 And .Address(False, False) = "C43" Then

  7. #7
    New Member
    Join Date
    Feb 2003
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Cell Equals then.....

      
    Thanks for the help guys,

    but I took the principle from what was suggested and did this which is working how I hoped.

    Code:
    Sub Graph_check_two()
    
    Workbooks("base.xls").Activate
    select_and_value = Worksheets("Front_Page").Range("C43").Select
    If select_and_value = 1 Then
    Application.Run "personal.xls!chart_wend"
    Else
    If select_and_value = 7 Then
    Application.Run "personal.xls!chart_wend"
    Else
    Application.Run "personal.xls!chart_week"
    End If
    End If
    
    End Sub
    Thanks for all the Input.

    Neil.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com