VBA problem
 

Greetings,

I have a spreadsheet in a format such as below
A B C D
1 1-Jan-03 to 31-Jan-03
2 Peak X
3 Off Peak Y
4 1-Feb-03 to 28-Feb-03
5 Peak X
6 Off Peak Y
7 Demand
8 1-Mar-03 to 31-Mar-03
9 Peak
10 Off Peak
11 Demand

What I want to do is work out if:

For each cell in column A If the month is equal to January
or February then
if cell within that month in column C equals "peak" then
cell in column D = X
else if cell within the month in column C equals off peak
then cell in column D = Y

1/ How I can instruct it to work out for each cell in
column A if it the month equals Jan or Feb?

2/ Secondly how can I write some code for it to recognoise
that "Peak" is part of January or under February...ie how
does it knows that Peak is part of January in C2 and part
of February in C6? In a nutshell I need some code for
it to recognise that between C2 and C3 it is January and
between C5-C7 it is February & C9-C11 is March. The
problem is in the next spreadsheet January could be C5-C15
or February could be C2-C4 so I cant specify exactly which
cells equate to which month as it will vary from one
spreadsheet to the next and is not set in stone.

Thanks in advance

Simon

VBA problem
 

Sub MarkColD()
Dim varr(1 To 12) As String
Dim lngMonth As Long
Dim i As Long
Dim sStr As String
Dim cell As Range, rng As Range
For i = 1 To 12
varr(i) = Format(DateSerial(2003, i, 1), "mmm")
Next
Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
sStr = cell.Text
For i = 1 To 12
If InStr(1, sStr, varr(i), 1) Then
lngMonth = i
Exit For
End If
Next
If InStr(1, cell.Text, "off peak", 1) Then
If lngMonth = 1 Or lngMonth = 2 Then
Cells(cell.Row, "D").Value = "Y"
End If
Else
If InStr(1, cell.Text, "peak", 1) Then
If lngMonth = 1 Or lngMonth = 2 Then
Cells(cell.Row, "D").Value = "X"
End If
End If
End If
Next

End Sub


worked with your test data.

Regards,
Tom Ogilvy





Simon wrote in message
...
Greetings,

I have a spreadsheet in a format such as below
A B C D
1 1-Jan-03 to 31-Jan-03
2 Peak X
3 Off Peak Y
4 1-Feb-03 to 28-Feb-03
5 Peak X
6 Off Peak Y
7 Demand
8 1-Mar-03 to 31-Mar-03
9 Peak
10 Off Peak
11 Demand

What I want to do is work out if:

For each cell in column A If the month is equal to January
or February then
if cell within that month in column C equals "peak" then
cell in column D = X
else if cell within the month in column C equals off peak
then cell in column D = Y

1/ How I can instruct it to work out for each cell in
column A if it the month equals Jan or Feb?

2/ Secondly how can I write some code for it to recognoise
that "Peak" is part of January or under February...ie how
does it knows that Peak is part of January in C2 and part
of February in C6? In a nutshell I need some code for
it to recognise that between C2 and C3 it is January and
between C5-C7 it is February & C9-C11 is March. The
problem is in the next spreadsheet January could be C5-C15
or February could be C2-C4 so I cant specify exactly which
cells equate to which month as it will vary from one
spreadsheet to the next and is not set in stone.

Thanks in advance

Simon