VBA: using string from an Input Box as criteria for a CountIF

[downsy]

Hi all

I'm trying to enter text (a staff members initials) into an Input Box so that the initials are then used as the citeria in a CountIF formula entered by VBA

A simplified version of the code I am using is:

Dim staffname As String
staffname = InputBox(Prompt:="You name please.", _
Title:="ENTER INITIALS", Default:="MC")

Range("H9").FormulaR1C1 = "=COUNTIF(RC[-3]:RC[-1],"" & staffname & "")"

The issue is getting the staffname string to be entered within the formula.

What is the correct combination of "" & to enable this to work.

Many thanks in advance for any advice offered.

 

[Scott Huish]

Range("H9").FormulaR1C1 = "=COUNTIF(RC[-3]:RC[-1],""" & staffname & """)"

 

[downsy]

thanks Scott, that looks remarkably like a variation I thought I tried - but obviously not!

works perfectly