Little help with VBA please and programming search in VBA

[bloomington]

Hi - first I want to thank everyone in advance for looking at my post and for any feedback given. Let me start by saying Im still pretty new to learning VBA and I have Excel 2007.

I have the following problem ...not sure how to program search and then actions after programming a search function.

I have 2 columns with the following:
COLUMN A COLUMN B


DEC 14 80 C
3


DEC 14 90 C
3


DEC 14 80 C
3


DEC 14 90 C
3


APR 15 80 P
19


APR 15 80 P
19



**Please note column A should have the DEC 14 80 C and column B should only have the numerical value...i couldnt line it up properly.

What I want to program is is the following...I want the program to look for the C (the stand alone one at the end in Column A) and if it locates a row with the stand alone C then to take the figure in column B ie 3. I want it to do this for all of column A and then for each row where the stand alone C is present to take the figure associated with it in Column B. Then I want it to add all of the values in column B that correspond to a row with the stand alone C and sum them up and place the value in E3. I want it to also do the same for locating all the rows with the stand alone P in Column A and take the associated figures and sum them up and place that value in E4. The reason I am calling them "stand alone C" is I want the program to look for that C (at the end) regardless of the month but as u can see in my example...DEC (December) has a C in it as well that isnt relevant and I dont want it to pick that up. I thought about breaking up the column into 2 columns putting the C or P (stand alone) in a separate column but some of my data doesnt line up properly so splitting the column into 2 wont work.

I hope someone can help me out as this is a component within a larger code and any and all help is greatly appreciated. I apologize for the novice question but I hope over time that as I progress I will be able to return what I learn to someone else who needs help in the future...simply not there yet however.

Thanks again.

Bloom

[SamT]

DEC 14 80 C
3


DEC 14 90 C
3


DEC 14 80 C
3


DEC 14 90 C
3


APR 15 80 P
19


APR 15 80 P
19



Put the table into Excel, Format the borders, Copy and paste it in your post


For Each Cel in Range("A:A")
Select Case Right(Cel, 1)
Case "C"
Range("E3") = Range("E3") + Cel.Offset(, 1)
Case "P"
Range("E4") = Range("E4") + Cel.Offset(, 1)
End Select
Next Cel

[bloomington]

Thanks very much Sam. That is exactly what I needed. Your help is much appreciated.

PS - thanks for the tip on lining up data in the forum for the future.

[snb]

or arrayformula ctrl shft Enter


=SUM((RIGHT(A1:A6;1)="C")*B1:B6)


=SUM((RIGHT(A1:A6;1)="P")*B1:B6)

[Jan Karel Pieterse]

Or:

=SUMIF(A1:A6,"* C",B1:B6)