CPSCount

[MCheru]

Here is the code I currently have.

Sub CPSCount()

Columns("B:C").Select
Selection.Copy
Sheets.Add
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Sheet3"
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("C1").Select
ActiveCell.FormulaR1C1 = "MP11"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MP12"
Range("E1").Select
ActiveCell.FormulaR1C1 = "MP20"

This is what my code currently does…

Sheet 1 copy columns B:C

Insert new worksheet (Sheet3)

Paste contents into new worksheet (Sheet3) starting in cell A1

Insert row above Row 1

In cell C1 type MP11, in cell D1 type MP12, and in cell E1 type MP20

These are the additional steps I would like my code to take…

Every cell in Column B has either MP11, MP12, or MP20, while every cell in
Column A has a 7 digit number, so the contents of Column A and Column B
belong together.

I want to keep a tally/count of how many times each seven digit number
appears in MP11, MP12, and MP20. In other words, I want to create a code
that will look at the number in Column A then look for MP11, MP12, or MP20 in
Column B. If Column B says MP11 then increase Column C by 1 in the same row,
if Column B says MP12 then increase Column D by 1 in the same row, if Column
B says MP20 then increase Column E by 1 in the same row. Sometimes the seven
digit number in Column A will be repeated. When that happens I want to
delete the row where the duplicate(s) appear(s) but increase the count in
Column C, D, or E (depending on the contents in Column B) by 1.

 

[Shane Devenshire]

Hi,

And the next time you enter a number in A1 do you want the value in A5 to be
increased by this amount or do you want the value adjusted by the difference
between the old value in A1 and then new value in A1?

If the first, then you will need to write a VBA macro.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, [A1])
If Not isect Is Nothing Then
[A5] = [A5] + [A1]
End If
End Sub

If the second then in A5

=A1+InitialValueOfA5

 

[MCheru]

I wanted the next time I entered a number in A1 the value in A5 to be
increased by this amount and that's what happened. Thanks so much for you're
help. The macro is great!

Hi,

And the next time you enter a number in A1 do you want the value in A5 to be
increased by this amount or do you want the value adjusted by the difference
between the old value in A1 and then new value in A1?

If the first, then you will need to write a VBA macro.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, [A1])
If Not isect Is Nothing Then
[A5] = [A5] + [A1]
End If
End Sub

If the second then in A5

=A1+InitialValueOfA5

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Here is the code I currently have.

Sub CPSCount()

Columns("B:C").Select
Selection.Copy
Sheets.Add
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Sheet3"
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("C1").Select
ActiveCell.FormulaR1C1 = "MP11"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MP12"
Range("E1").Select
ActiveCell.FormulaR1C1 = "MP20"

This is what my code currently does…

Sheet 1 copy columns B:C

Insert new worksheet (Sheet3)

Paste contents into new worksheet (Sheet3) starting in cell A1

Insert row above Row 1

In cell C1 type MP11, in cell D1 type MP12, and in cell E1 type MP20

These are the additional steps I would like my code to take…

Every cell in Column B has either MP11, MP12, or MP20, while every cell in
Column A has a 7 digit number, so the contents of Column A and Column B
belong together.

I want to keep a tally/count of how many times each seven digit number
appears in MP11, MP12, and MP20. In other words, I want to create a code
that will look at the number in Column A then look for MP11, MP12, or MP20 in
Column B. If Column B says MP11 then increase Column C by 1 in the same row,
if Column B says MP12 then increase Column D by 1 in the same row, if Column
B says MP20 then increase Column E by 1 in the same row. Sometimes the seven
digit number in Column A will be repeated. When that happens I want to
delete the row where the duplicate(s) appear(s) but increase the count in
Column C, D, or E (depending on the contents in Column B) by 1.