How to fill value in cell based on date criteria?

[Mslady]

If value is column A is Jan-05, fill in value of 222,222 in matching column E.
If value in column A is Feb-05, fill in value of 333,333 in matchng column E.
Column A is in this format (3/12/05).

How do i achieve this with VBA?
Thanks in advance

[cush]

Without knowing how many rows you want to check, or whether the pattern in
Col E
will continue adding 111111 each time (which is assumed in the following
code) you might try something like the following. Adjust as needed

Option Explicit

Sub CheckDate()
Dim i As Integer, j As Integer, k As Long

j = 1
k = 222222

For i = 1 To 2 'number of rows to check
If Cells(i, 1) >= DateValue(j & "/1/2005") _
And Cells(i, 1) <= DateValue(j + 1 & "/1/2005") - 1 Then
Cells(i, 5) = k
Else
'do nothing or maybe something else
End If
j = j + 1
k = k + 111111

Next i

End Sub

"Mslady" wrote:

>
> If value is column A is Jan-05, fill in value of 222,222 in matching
> column E.
> If value in column A is Feb-05, fill in value of 333,333 in matchng
> column E.
> Column A is in this format (3/12/05).
>
> How do i achieve this with VBA?
> Thanks in advance
>
>
> --
> Mslady
> ------------------------------------------------------------------------
> Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776
> View this thread: http://www.excelforum.com/showthread...hreadid=480299
>
>

[Mslady]

Hi Cush, thanks for you prompt response:

but what i want is to check range of column A.
the date is in this format month-day-year i.e. "3/12/05" meaning March 12, 2005.
No Column E will be any value.

If column A is January 2005 fill in 949007 in corresponding cells in column E
If column A is February 2005 put 104332

I am only doing for these 2 months. And the values to be added are fixed. I just used 11111 and 22222 to give examples, it's not continuous for all the months.

Thanks.

Without knowing how many rows you want to check, or whether the pattern in
Col E
will continue adding 111111 each time (which is assumed in the following
code) you might try something like the following. Adjust as needed

Option Explicit

Sub CheckDate()
Dim i As Integer, j As Integer, k As Long

j = 1
k = 222222

For i = 1 To 2 'number of rows to check
If Cells(i, 1) >= DateValue(j & "/1/2005") _
And Cells(i, 1) <= DateValue(j + 1 & "/1/2005") - 1 Then
Cells(i, 5) = k
Else
'do nothing or maybe something else
End If
j = j + 1
k = k + 111111

Next i

End Sub

"Mslady" wrote:

>
> If value is column A is Jan-05, fill in value of 222,222 in matching
> column E.
> If value in column A is Feb-05, fill in value of 333,333 in matchng
> column E.
> Column A is in this format (3/12/05).
>
> How do i achieve this with VBA?
> Thanks in advance
>
>
> --
> Mslady
> ------------------------------------------------------------------------
> Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776
> View this thread: http://www.excelforum.com/showthread...hreadid=480299
>
>

[Nigel]

Mslady

This will read the whole of column A on the active sheet and check column A
for dates, if the month is 1 or 2 then the values 1111111 or 2222222 are
placed in column E. If you need to detect other months add additional
conditions, change the assignment values (e.g. 111111 and 222222) as
required.
You might wish to place a line after the If IsDate(.Value) Then to force
column E value to a blank - in case a change of date occurs and the value is
no longer valid for that date. E.g. .Cells(xr,5) = ""

Dim xlr As Long, xr As Long
With ActiveSheet
xlr = .Cells(Rows.Count, "A").End(xlUp).Row
For xr = 1 To xlr
With .Cells(xr, 1)
If IsDate(.Value) Then
If Month(.Value) = 1 Then .Cells(xr, 5) = "111111"
If Month(.Value) = 2 Then .Cells(xr, 5) = "222222"
End If
End With
Next xr
End With


--
Cheers
Nigel



"Mslady" <[email protected]> wrote in
message news:[email protected]...
>
> If value is column A is Jan-05, fill in value of 222,222 in matching
> column E.
> If value in column A is Feb-05, fill in value of 333,333 in matchng
> column E.
> Column A is in this format (3/12/05).
>
> How do i achieve this with VBA?
> Thanks in advance
>
>
> --
> Mslady
> ------------------------------------------------------------------------
> Mslady's Profile:
http://www.excelforum.com/member.php...o&userid=27776
> View this thread: http://www.excelforum.com/showthread...hreadid=480299
>

[Mslady]

Brilliant!!!!
This works perfectly. Thanks Nigel