Dynamic Range in Formulas VBA

[Maxpsb47]

Hi,

How can I make the range in a formula to work with a dynamic range?

* 'R2C5:R640C5' & 'R2C5:R45C5' (shown below) is a variable range on the spreadsheet and not a fixed one.
* Attached example only has what's applicable to the questions.

1. COUNTIF: Used to count the number of repetitions on each cell value (Item#). That'll be Column A in the attached example.

ActiveCell.FormulaR1C1 = "=COUNTIF(R2C5:R640C5,RC[-6])"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & Range("C" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

2. INDEX & MATCH: Used to lookup and match 'Item#' and return 'HA#'. That'll be lookup Column A and match with Column B in Sheet#2, then return Column A from Sheet#2 to Column C.

Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('HA Data'!R2C4:R45C4,MATCH('Sample Data'!RC[-7],'HA Data'!R2C5:R45C5,0),1)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & Range("C" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Hopefully this makes sense.

[TMS]

Try:

Option Explicit

'1. COUNTIF: Used to count the number of repetitions on each cell value (Item#). That'll be Column A in the attached example.

Sub Test1()

Dim lLR As Long
lLR = Range("C" & Rows.Count).End(xlUp).Row

Dim lLR_HA As Long
lLR_HA = Sheets("HA Data").Range("A" & Rows.Count).End(xlUp).Row

Range("K2:K" & lLR).Formula = _
    "=COUNTIF($E$2:$E$" & lLR_HA & ",E2)"

End Sub

'2. INDEX & MATCH: Used to lookup and match 'Item#' and return 'HA#'. That'll be lookup Column A and match with Column B in Sheet#2, then return Column A from Sheet#2 to Column C.

Sub Test2()

Dim lLR As Long
lLR = Range("C" & Rows.Count).End(xlUp).Row

Dim lLR_HA As Long
lLR_HA = Sheets("HA Data").Range("A" & Rows.Count).End(xlUp).Row

Range("L2:L" & lLR).Formula = _
    "=INDEX('HA Data'!$D$2:$D$" & lLR_HA & ",MATCH('Sample Data'!E2,'HA Data'!$E$2:$E$" & lLR_HA & ",0),1)"

End Sub


'1. COUNTIF: Used to count the number of repetitions on each cell value (Item#). That'll be Column A in the attached example.

Sub Test3()

ActiveCell.FormulaR1C1 = "=COUNTIF(R2C5:R640C5,RC[-6])"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & Range("C" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

End Sub

'2. INDEX & MATCH: Used to lookup and match 'Item#' and return 'HA#'. That'll be lookup Column A and match with Column B in Sheet#2, then return Column A from Sheet#2 to Column C.

Sub Test4()

Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('HA Data'!R2C4:R45C4,MATCH('Sample Data'!RC[-7],'HA Data'!R2C5:R45C5,0),1)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & Range("C" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

End Sub

[Maxpsb47]

Thanks for the reply!

The INDEX & MATCH works perfectly, but not the COUNTIF part. It's only returning 0's & 1's.

So the value in column E go as 'Item# 1, Item# 2, and so on'. There should be 3 of each 'Item#'. I'm just using the COUNTIF formula to check this throughout the spreadsheet.

[beyond Excel]

So the value in column E go as 'Item# 1, Item# 2, and so on'. There should be 3 of each 'Item#'. I'm just using the COUNTIF formula to check this throughout the spreadsheet.

Sorry but you keep mentioning column E. It turns out that neither of the two workbook sheets you uploaded to the forum contain any information in column E.

And that's why I asked you to upload another workbook to the Forum but with that information corrected.

[Maxpsb47]

Sorry for the confusion. I was avoiding having to upload the whole spreadsheet. Thank for the help though.

[beyond Excel]

Thanks for the reply!
The INDEX & MATCH works perfectly...

Like TMS, it's worth thanking you for the rep.

[beyond Excel]

Hello.
The workbook you uploaded to the Forum doesn't help to show you how to use COUNTIF.

However, it is possible to make the use of the other formula dynamic (I added IFERROR). For example like this:

Sub Example21()
Dim sTxt1$, sTxt2$

With Range("'HA Data'!A1").CurrentRegion
  sTxt1 = .Address(external:=True)
  sTxt2 = .Columns(2).Address(external:=True)
End With

With Sheets("Sample Data")
  With .Range("C2:C" & .Cells(Rows.Count, "A").End(xlUp).Row)
    .Formula = "=IFERROR(INDEX(" & sTxt1 & ", MATCH(A2, " & sTxt2 & ", 0), 1), ""---"")"
    .Value = .Value
  End With
End With
End Sub 

[TMS]

Your formula, as coded in R1C1 style, refers to column E. if you want to count entries in column A . . .

Range("K2:K" & lLR).Formula = _
    "=COUNTIF($A$2:$A$" & lLR_HA & ",A2)"

And the sample formula in the workbook also refers to column E although it is IN column E.

You do not have any data in column E other than the sample formulae.

If you make a sample workbook, stick to the structure of the live file.

[Maxpsb47]

Ok, I figured it out. I just needed to change the 1LR_HA to 1LR in the COUNTIF formula. Both the 'Sample Data' & 'HA Data' worksheet have different row counts. Now it works.

Thanks again man. Appreciate the help!

[TMS]

You're welcome. Thanks for the rep.