if, else if with date ranges

[Mel L]

Hello All,

I am trying to write a code that will copy data from one workbook (one sheet) to another workbook. I can do that. My problem is, I want the data pasted on a specific worksheet in second workbook based on a date range. Say if the cell in B1 of first worksheet contains a date, and that date is between 6/1/07 and 8/31/07 i want it to go to sheet 1 in second workbook. 4 worksheets in second workbook (4 quarters in a year).

If Range(b1) >= #6/1/2007# And Range(b1) <= #8/31/2007# Then Sheets("1st qtr").Select
    ElseIf Range(b1) >= #9/1/2007# And Range(b1) <= #11/30/2007# Then Sheets("2nd qtr").Select
    ElseIf Range(b1) >= #12/1/2007# And Range(b1) <= #2/28/2008# Then Sheets("3rd qtr").Select
    ElseIf Range(b1) >= #3/1/2008# And Range(b1) <= #5/31/2008# Then Sheets("4th qtr").Select
    End If

This is my code. It stops at the second line of code after the 'And' at the 'Range' and I get an error message telling me:

Compile Error:
Else without If

I guess I'm dense, but I just don't understand why it's debugging.

 

[MorganO]

You have a couple of minor errors in your code. First, for the If Then Else construct you must start a newline after the 'Then' statement. Also, you need to place the cell name in quotation makers within the Range statement. Here is the fixed code:

If Range("b1") >= #6/1/2007# And Range("b1") <= #8/31/2007# Then
  Sheets("1st qtr").Select
ElseIf Range("b1") >= #9/1/2007# And Range("b1") <= #11/30/2007# Then
  Sheets("2nd qtr").Select
ElseIf Range("b1") >= #12/1/2007# And Range("b1") <= #2/28/2008# Then
  Sheets("3rd qtr").Select
ElseIf Range("b1") >= #3/1/2008# And Range("b1") <= #5/31/2008# Then
  Sheets("4th qtr").Select
End If

Take care

Owen

 

[Mel L]

Ok, I made the suggested changes. That works for keeping it from debugging, but for some reason it isn't selecting the correct sheet. Any suggestions?

 

[Mel L]

oh, by the way (i forgot my manners) many thanks for looking into this for me. i was about to go insane.

 

[MorganO]

Hmm... I tested the code on my end and it worked fine. You might need to be more specific in your code as to the range you are refering to. In your code, the currently selected sheet will be where the range is refered to.

You can either do a sheets("Your sheet").activate prior to running this code

Sheets("Your Sheet").activate
If Range("b1") >= #6/1/2007# And Range("b1") <= #8/31/2007# Then 
  Sheets("1st qtr").Select 
ElseIf Range("b1") >= #9/1/2007# And Range("b1") <= #11/30/2007# Then 
  Sheets("2nd qtr").Select 
ElseIf Range("b1") >= #12/1/2007# And Range("b1") <= #2/28/2008# Then 
  Sheets("3rd qtr").Select 
ElseIf Range("b1") >= #3/1/2008# And Range("b1") <= #5/31/2008# Then 
  Sheets("4th qtr").Select 
End If

or you can place sheets("your sheet"). in front of each Range("b1") statement.

If Sheets("your sheet").Range("b1") >= #6/1/2007# And Sheets("your sheet").Range("b1") <= #8/31/2007# Then 
  Sheets("1st qtr").Select 
ElseIf Sheets("your sheet").Range("b1") >= #9/1/2007# And Sheets("your sheet").Range("b1") <= #11/30/2007# Then 
  Sheets("2nd qtr").Select 
ElseIf Sheets("your sheet").Range("b1") >= #12/1/2007# And Sheets("your sheet").Range("b1") <= #2/28/2008# Then 
  Sheets("3rd qtr").Select 
ElseIf Sheets("your sheet").Range("b1") >= #3/1/2008# And Sheets("your sheet").Range("b1") <= #5/31/2008# Then 
  Sheets("4th qtr").Select 
End If

Hope this helps.

Owen

 

[Mel L]

Many thanks. The problem is that B1 is on another sheet. You are correct. I appreciate all the help.