Very easy VBA problem!

[imakaveli]

Hello guys, I'm a very newbie from Italy, I'm using VBA and I have a very elementary problem:

I basically want to check if in a cell there is a certain word, it there is I want the counter to count it, if not, let's skip to the next cell, here is my little code:


Dim MBI, LBO, MBO as String (I have used Variant as well but it doesn't work)
for r=2 to 4
if Sheets("Foglio1").cells(r,29).Value = "LBO", "MBO", "MBI" then
counter = counter + 1
Else: counter = counter + 0
End If
Next r

It's like it doesn't check if there are these words in the cells... I can do it with numbers but not with words :( I think it's a very easy problem but I cannot do it!

Thank you very much for all the suggestions you can give me

Regards
Ale

[Bob Phillips]

Dim MBI, LBO, MBO as String (I have used Variant as well but it doesn't work)
With Sheets("Foglio1").cells(r,29)
for r=2 to 4
if .Value = "LBO" Or .Value = "MBO" Or .Value = "MBI" then
counter = counter + 1
End If
Next r
End With

[imakaveli]

Thank you very much xld, now it works!!! :)

[imakaveli]

Excuse me, I have another question:

I have for example 3 columns: the first one indicate a sector, the second one indicate the month in numbers (1=January, 2=February an so on) and the third column indicates the year (1999, 2000, 2001), for example is like this:

garden 4 1999
money 6 2000
food 3 2000
pharmacy 2 2001
food 9 2001
money 11 2001
garden 10 2002

I need a fourth column in which to put an index: for each row I want to calculate the number of months from the last case of the same sector, for example in the first case the index is 0 cause is the first one, and the second one is 0 as well because is the first money we have, but in the fifth row we have to put 18 because 18 months have passed since the last food case.

The final index must be:
0
0
0
0
18
17
26

It's a problem cause I don't have a complete date but I have months and years separated! Do you have any ideas how to solve the problem?
Thanks so much for all the tips you'll give me
cheers
Ale

[Bob Phillips]

Use these formulae

C2: =IF(COUNTIF($A$1:$A2,$A2)=1,0,MAX(IF($A$1:$A1=$A2,ROW($A$1:$A1)))) (this is an array formula)

copy C2 down


D1: 0
D2: =IF($D2=0,0,DATEDIF(DATE(INDEX($C$1:$C1,$D2),INDEX($B$1:$B1,$D2),1),DATE($C 2,$B2,1),"M"))

copy D2 down

[imakaveli]

Use these formulae

C2: =IF(COUNTIF($A$1:$A2,$A2)=1,0,MAX(IF($A$1:$A1=$A2,ROW($A$1:$A1)))) (this is an array formula)

copy C2 down


D1: 0
D2: =IF($D2=0,0,DATEDIF(DATE(INDEX($C$1:$C1,$D2),INDEX($B$1:$B1,$D2),1),DATE($C 2,$B2,1),"M"))

copy D2 down

Thank you very much for your formula xld, sorry if this is a late reply but I was sure I said thanks before! :)

Cheers
iMak