Enter a Coutif formula not working with VBA 2

[Eprice]

Hi,
I have a form in excel where the user can add another row according to what name they enter on the form. Then it puts it in the corrrect order on the worksheet (using a sheet called Main) and adds another sheet with that name. My problem is trying to get the formula in the cells of the new row. The code I have puts the formula in the cells but it shows #### or #Name? in the cells. If I click in the formula bar and hit enter it works but the users don't want to do that. Can anyone tell me how to do that with visual basic. I tried Sendkeys "{ENTER}" after the countif line but that only wants to save each time. Here is the entire code I am using for the form. (Probably too much info but you can see where variables are coming from.) Thanks for any help. Lisa
I have two text boxes on the form, txtAuditor and txtBranch

Dim varAuditor, varBranch, c
varAuditor = txtAuditor
'---GET THE LAST NAME ONLY FROM THE FORM
While InStr(varAuditor, " ") > 0
N = InStr(1, varAuditor, " ")
varAuditor = Mid(varAuditor, N + 1)
Wend
Sheets("Main").Visible = True
Sheets("Main").Select
'---ENTER THE LAST NAME ON THE HIDDEN 'MAIN' SHEET
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select
Range("A3") = varAuditor
'---SORT THE RANGE TO PUT NEW AUDITOR IN CORRECT ORDER
Application.Goto Reference:="Auditors"
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'--GET THE ROW NUMBER OF THE NEW AUDITOR FROM THE MAIN SHEET
With Worksheets("Main").Range("Auditors")
Set c = .Find(varAuditor, lookin:=xlValues)
If Not c Is Nothing Then
c.Select
varRow = ActiveCell.Row
varPrevRow = ActiveCell.Row - 1
varNextRow = ActiveCell.Row + 1
End If
'---ENTER THE NEW AUDITOR ON SUMMARY IN THE CORRECT ORDER ACCORDING TO ROW #
Sheets("Main").Visible = False
Sheets("Summary").Select
Rows(varRow).Select
Selection.Insert Shift:=xlDown
Range("A" & varRow).Select
Range("A" & varRow) = txtAuditor
Range("B" & varRow) = txtBranch
'---COPY SHEET BEFORE NEW AUDITOR AND PASTE IN CORRECT PLACE
Range("A" & varPrevRow).Select
varPrevAuditor = Range("A" & varPrevRow).Value
Sheets(varPrevAuditor).Select
Application.CutCopyMode = False
Sheets(varPrevAuditor).Copy Before:=Sheets(varRow)
'---RENAME SHEET TO NEW AUDITOR NAME
ActiveSheet.Name = txtAuditor
'---ADD BRANCH TO HEADER
ActiveSheet.PageSetup.LeftHeader = "&""Arial,Bold""&12" & txtBranch
'---ADD FORMULAS ON SUMMARY SHEET FOR NEW AUDITOR ROW ADDED
Sheets("Summary").Select
Rows(varRow).Select
Range("C" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!C:C, ""X"")"
Range("D" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!D, ""X"")"
Range("E" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!E:E, ""X"")"
Range("F" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!F:F, ""X"")"
Range("G" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!G:G, ""X"")"
Range("H" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!H:H, ""X"")"
Range("I" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!I:I, ""X"")"
Range("J" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!J:J, ""X"")"
Range("K" & varRow).FormulaR1C1 = "=COUNTIF('" & txtAuditor & "'!K:K, ""X"")"
Range("L" & varRow).FormulaR1C1 = "=SUM(C" & varRow & ":K" & varRow & ")"
'---SELECT NEW AUDITOR SHEET
Sheets(txtAuditor).Select
End With
Unload Me

 

[PHV]

Why using a A1 notation when a R1C1 is expected ?
Range("C" & varRow).Formula = "=COUNTIF('" & txtAuditor & "'!C:C, ""X"")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eprice]

PHV
Well, I have no idea. When I recorded a macro to get an idea for what to write in the code, it recorded the R1C1 so I used it for every cell. Honestly I don't know what you mean by an A1 notation or what R1C1 is. I took the R1C1 out of all the lines and it works perfectly. Thank you VERY much for the help. Lisa

 

[xlbo]

Lisa

R1C1 notation is a different way of referring to cells.

A1 represents a definition of a cell - colA, row 1

R1C1 also represents it but in the format of Row 1, Column 1

For some reason the macro recorder always uses R1C1 notation but as PHV has alluded to, there are 2 (actually more than 2 but that is for a later date) 'formula' properties for a cell, the formula property and the formulaR1C1 property.

May I suggest that it would be well worthwhile having a look at the excel object model (press F2 when inside the VBE) as you can select an object and see ALL its associated properties and methods. You can then right click on any property and select the help file for it - much easier than trying to find the right keyword in the search part of the help files IMHO