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

How do i achieve this with VBA?
Thanks in advance ;

--
Mslad
-----------------------------------------------------------------------
Mslady's Profile: http://www.excelforum.com/member.php...fo&userid=2777
View this thread: http://www.excelforum.com/showthread.php?threadid=48029

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

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"* meanin
March 12, 2005.
No Column E will be any value.

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

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

Thanks. :)

cush Wrote:
Without knowing how many rows you want to check, or whether the patter
in
Col E
will continue adding 111111 each time (which is assumed in th
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



--
Mslad
-----------------------------------------------------------------------
Mslady's Profile: http://www.excelforum.com/member.php...fo&userid=2777
View this thread: http://www.excelforum.com/showthread.php?threadid=48029

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" wrote in
message ...

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

Brilliant!!!!
This works perfectly. Thanks Nigel :)


--
Mslady
------------------------------------------------------------------------
Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776
View this thread: http://www.excelforum.com/showthread...hreadid=480299