VBA Formulas

[Guest]

Hi,
Basically I am trying to write a macro that writes formulas based on a list of headers

=COUNTIF('Raw Data'!E2:E46, "F02")

the function so far --

With Range("C22")
.FormulaR1C1 = "F02"
.Offset(0, 1).FormulaR1C1 = "=COUNTIF('Raw Data'!R[2]C[1]:R[46]C[1], ""F02"")"
.Offset(0, 2).FormulaR1C1 = "Array Formula"
End With

The formula was recorded using macro recorder. It's not working as the arguments.. R[2]C[1]:R[46]C[1], produced the formula =COUNTIF('Raw Data'!E42:E65534, "F02") instead of =COUNTIF('Raw Data'!E2:E46, "F02"). Help!

 

[Dave Hawley]

Hi

The code that you have recorded is relative to the cell it's entered in. If you always want a relative reference to always be the same try:

Dim strAdd As String
strAdd = Range("E2:E46").Address _
(RowAbsolute:=False, columnAbsolute:=False, _
ReferenceStyle:=xlR1C1, RelativeTo:=Range("D22"))

With Range("C22")
.FormulaR1C1 = "F02"
.Offset(0, 1).FormulaR1C1 = "=COUNTIF('Raw Data'!" & strAdd & ", ""F02"")"
.Offset(0, 2).FormulaR1C1 = "Array Formula"
End With