VBA formula help...

Matty

Well-known Member
Hi,

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

Code:
``````    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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Try

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

Or to do it without looping, try
Code:
``````    With .Range("A2:A" & LR)
.Formula = "=COUNTIF(RC[2]:R" & LR & "C[2],RC[2])"
.Value = .Value
End With``````

Last edited:
Thanks Jonmo1! That got it.

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.

Something like this
Code:
``````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 ..
Code:
``Const f As String = "=COUNTIF(C2:C\$#,C2)"``
.. which is much easier to read as it looks pretty much like a standard worksheet formula.

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.

Something like this

Code:
``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 ..

Code:
``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

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.

Replies
2
Views
100
Replies
4
Views
478
Replies
3
Views
163
Replies
3
Views
400
Replies
16
Views
310

1,196,057
Messages
6,013,166
Members
441,751
Latest member
336448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back