Enter Cell Value According to Date in other Cell 1

[BatFace]

Thanks in advance for any help you can give me!

I am trying to do a loop which will look at the date entry in one cell then enter a string into another according to the value (eg if a1 is between 1/7/03 and 31/7/03 then d1 = "July NY&quot...The code I am using (it's messy I know but I am a novice at VBA) is below...I have 2000 rows that I need to loop thru and the date range will be from now to 2 years in the future - I think if I continued using this code it will take a long time to process.

If ActiveCell.Value >= 1 / 7 / 2 And ActiveCell.Value <= 31 / 7 / 2 Then
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = &quot;Jul TY&quot;
ActiveCell.Offset(1, -3).Select

Thanks if you can help me!!!

 

[kevinclark]

Maybe I am off the mark but you can see if something like this will work for you.

To test this example:

1) Create a new Workbook
2) In Sheet1 Range A1 enter 1/7/2002
3) In Sheet1 Range A2 enter 3/1/2002
4) In Sheet1 Range A3 enter 2/27/2002
5) In Sheet1 Range A4 enter 3/8/2002
6) In Sheet2 Range A1 type the words Start Date
7) In Sheet2 Range B1 type the words End Date
8) In Sheet2 Range C1 type the word Month
9) In Sheet2 Range D1 type the word State
10) In Sheet2 Add the following data as indicated below

StartDate EndDate Month State
1/1/2002 1/31/2002 JAN FL
2/1/2002 3/5/2002 FEB AZ 'Notice this date range
3/6/2002 3/31/2002 JAN NY


11) Copy and paste this code into a module
12) Create a button on Sheet1 and assign this macro to it


Sub Sample()
Dim WS1 As String 'Worksheet 1
Dim WS2 As String 'Worksheet 2
Dim WS1Row As Long 'Row Position for Worksheet 1
Dim WS2Row As Long 'Row Position for Worksheet 1
Dim WS1Date As Date 'Date to look up
Dim WS2StartDate As Date 'Between Start Date
Dim WS2EndDate As Date 'and End Date

WS1 = &quot;Sheet1&quot;
WS2 = &quot;Sheet2&quot;


For WS1Row = 1 To 2000
If Sheets(WS1).Cells(WS1Row, 1).Value <> &quot;&quot; Then
WS1Date = CDate(Sheets(WS1).Cells(WS1Row, 1).Value)
For WS2Row = 2 To 100
WS2StartDate = CDate(Sheets(WS2).Cells(WS2Row, 1).Value)
WS2EndDate = CDate(Sheets(WS2).Cells(WS2Row, 2).Value)
If WS1Date >= WS2StartDate And WS1Date <= WS2EndDate Then
Sheets(WS1).Cells(WS1Row, 2).Value = Sheets(WS2).Cells(WS2Row, 3).Value _
& &quot; &quot; & Sheets(WS2).Cells(WS2Row, 4).Value
End If
Next WS2Row
End If
Next WS1Row


End Sub


Hope it is of some help

 

[BatFace]

Thanks Kevin it worked beautifully...I made a little change to suit what I needed and it's fine - so fast and simple.
I have given you a star for taking the time to explain what each variable was referring too also - it makes it much easier to understand the code for a wee novice such as myself....

Thanks again!
Katie