VBA formula help...

[Matty]

Hi,

Mind's gone blank. Can someone remind me how I would write this:

    For i = 2 To LR
        With .Range("A" & i)
            .Formula = "=COUNTIF(RC[2]:[COLOR=#ff0000]R4913C[2][/COLOR],RC[2])"
            .Value = .Value
        End With
    Next i

So that the highlighted part always references the last cell based on the LR?

Thanks,

Matty

 

[Jonmo1]

Try

.Formula = "=COUNTIF(RC[2]:R" & LR & "C[2],RC[2])"


Or to do it without looping, try

    With .Range("A2:A" & LR)
        .Formula = "=COUNTIF(RC[2]:R" & LR & "C[2],RC[2])"
        .Value = .Value
    End With

 

[Matty]

Thanks Jonmo1! That got it.

Matty

 

[Peter_SSs]

Another way that I often find useful, particularly if LR needs to be incorporated in several places in the formula, is as follows.
Rather than breaking the formula up into bits and manually sticking LR in each relevant place is to write the formula as you want it but with a placeholder for LR then replace the placeholder(s) in bulk.
To me it has the added advantage of making the formula more readable.

Something like this

Const f As String = "=COUNTIF(RC[2]:R#C[2],RC[2])"

With .Range("A2:A" & LR)
    .Formula = Replace(f, "#", LR, 1, -1, 1)
    .Value = .Value
End With

Also, if we know we are staring in row 2 (which appears to be the case form your loop) and counting in column C then my Const statement would become ..

Const f As String = "=COUNTIF(C2:C$#,C2)"

.. which is much easier to read as it looks pretty much like a standard worksheet formula.

 

[Matty]

Another way that I often find useful, particularly if LR needs to be incorporated in several places in the formula, is as follows.
Rather than breaking the formula up into bits and manually sticking LR in each relevant place is to write the formula as you want it but with a placeholder for LR then replace the placeholder(s) in bulk.
To me it has the added advantage of making the formula more readable.

Something like this

Const f As String = "=COUNTIF(RC[2]:R#C[2],RC[2])"  With .Range("A2:A" & LR)     .Formula = Replace(f, "#", LR, 1, -1, 1)     .Value = .Value End With

Also, if we know we are staring in row 2 (which appears to be the case form your loop) and counting in column C then my Const statement would become ..

Const f As String = "=COUNTIF(C2:C$#,C2)"

.. which is much easier to read as it looks pretty much like a standard worksheet formula.

Thanks Peter. That's a good tip!

Matty

 

[Rick Rothstein]

Another way that I often find useful, particularly if LR needs to be incorporated in several places in the formula, is as follows.
Rather than breaking the formula up into bits and manually sticking LR in each relevant place is to write the formula as you want it but with a placeholder for LR then replace the placeholder(s) in bulk.

I like that idea! Especially since, beside "neatening things up", it would also allow my one-liners to remain one-liners.