When I type a word, a formula will be entered in the cell in column D on the same row

[clarksonneo]

Hi,

I hope that:
When I type a word in column B, the cell in column D on the same row will be entered a formula that can count the number of that word in column B.

The following picture is my expected result.

Eg, when a typed "A" in cell B5, cell D5 will be entered =COUNTIF(B:B,B5)

Eg, if I haven't type anything in cell B10, cell D10 is empty.

Could you please amend my code so that my marco can produce the expected result?
If possible, please use select case, as I want to learn it.

[vba]Sub CountWhenType()

Dim i As Integer
Dim a As Range
a = Cells(i, 2).Value

Select Case a

Case ""
Cells(i, 4).Value = ""
Case Else
Cells(i, 4).Formula = "COUNTIF(C:C, B & i )"

End Select

End Sub
[/vba]

Thanks

[mikerickson]

You could put this in the sheet's code module.
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim oneCell As Range
On Error GoTo ErrorHalt
Application.EnableEvents = False

For Each oneCell In Application.Intersect(Columns(2), Target)
With oneCell
Select Case CStr(.Value)
Case vbNullString
.Offset(0, 2).Value = vbNullString
Case Else
.Offset(0, 2).FormulaR1C1 = "=COUNTIF(C2:C2, RC2)"
End Select
End With
Next oneCell
ErrorHalt:
Application.EnableEvents = True
End Sub[/VBA]

[clarksonneo]

You could put this in the sheet's code module.
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim oneCell As Range
On Error GoTo ErrorHalt
Application.EnableEvents = False

For Each oneCell In Application.Intersect(Columns(2), Target)
With oneCell
Select Case CStr(.Value)
Case vbNullString
.Offset(0, 2).Value = vbNullString
Case Else
.Offset(0, 2).FormulaR1C1 = "=COUNTIF(C2:C2, RC2)"
End Select
End With
Next oneCell
ErrorHalt:
Application.EnableEvents = True
End Sub[/vba]

Hi,

can i ask you a question in your marco?

in there, you write
=COUNTIF(C2:C2, RC2)

However, when I replace RC2 by RC20, R, C, and even a,
the marco will still provide the same result.

Why that is the case?

Thanks

[Aussiebear]

because the formula uses that value as the value to be counted within the column C