Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What if statement on two sheets (VBA Excel 2000)

    I'm looking for a "what if" statement that will cover two conditions on two ranges in two worksheets in the same file. Let's say I have one sheet called sheet 1 and another named sheet 2. I would usually first select the sheet with a "Sheets("sheet1").Select" then I would say If Range("a1").Value <0> And Range ("b1').Value= 2 Then" whatever my action is. This is easy on one sheet because VBA knows the ranges I'm talking about are on the sheet I selected.

    My problem is how can I identify two ranges on two different sheets in my "What If" statement? Naming the ranges is not an option.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What if statement on two sheets (VBA Excel 2000)

    You don't need to select cells to inspect their value. You can use something like this:

    If Worksheets("Sheet1").Range("A1") <> 0 And Worksheets("Sheet2").Range("B1") = 2 Then

    You can even refer to cells in different workbooks:

    If Workbooks("This.xls").Worksheets("Sheet1").Range(" A1") <> 0 And Workbooks("That.xls").Worksheets("Sheet2").Range(" B1") = 2 Then

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What if statement on two sheets (VBA Excel 2000)

    Makes sense. Thanks I'll try it.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What if statement on two sheets (VBA Excel 2000)

    Not workilng. I'm getting an "Out of Range" error. I've checked the sheet names etc. Here's a sample:

    "If Sheets("Spreadsheet").Range("PRNCODE").Value = 2 And Sheets("(Spreadsheet (2)").Range("g351").Value <> 0 Then
    cashflowconvert2
    ElseIf Sheets("Spreadsheet").Range("PRNCODE").Value = 2 And Sheets("(Spreadsheet (2)").Range("g351") = 0 Then
    gotospreadsheet
    End If

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: What if statement on two sheets (VBA Excel 2000)

    Do you actually have a sheet within the workbook called "Spreadsheet"? You need to get the sheet names exactly correct. This:

    Sheets("(Spreadsheet (2)")

    should probably be:

    Sheets("Spreadsheet (2)")

    If the name of the sheet tab is actually "Budget 05" then the code reference would be:

    Sheets("Budget 05")
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What if statement on two sheets (VBA Excel 2000)

    Is PRNCODE a named range, and does it consist of a single cell?

    Perhaps you could post a stripped down copy of your workbook, with sensitive data replaced with dummy data.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What if statement on two sheets (VBA Excel 2000)

    You are right. I had too many Parentheses. It works now.

    For the record, yes the sheet's name is Spreadsheet and the range "Prncode" is one cell.

    Thanks all for your help.

Posting Permissions

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