Dynamic Range in Formulas VBA

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:

PHP Code: 

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 


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.

Originally Posted by Maxpsb47

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.

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!

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

Originally Posted by Maxpsb47

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

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