0

In Excel VBA, I created a code for a data table that had 12,500 rows. Later, I found the same code would be valuable for data tables with different sizes.

To make it easier, I converted all of the ranges into strings, so that I would only need to change the strings at the top of the code once, instead of each time the range was referenced.

This small piece of the code has an example. By declaring "Group_key" at the top, that range would automatically flow into the code. I did this for about 30 ranges, but there is one range I can't figure out.

In the "COUNTIF" equation, it references a relative range. I would like to convert "R6C2:R12500C2" into a string, but when I tried that, it automatically included quotes around the range, and the COUNTIF function doesn't work.

Would anyone know how to solve this?

Sub Format()
Dim Group_key As String
Group_key = "A5:A12500"

Range("AT6").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "=COUNTIF(R6C2:R12500C2,RC[-44])"
    Selection.AutoFill Destination:=Range(Group_key)

End Sub
Community
  • 1
  • 1
milaske
  • 55
  • 2
  • 8
  • Please avoid `.Select` and `Active` references as it's slow and sometime dangerous... please see [***Avoiding .Select/.Activate***](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Maldred Nov 30 '17 at 18:57
  • Possible duplicate/related of [VBA Range from String](https://stackoverflow.com/questions/10403794/vba-range-from-string). – Matt Nov 30 '17 at 19:12
  • Ultimately what are you to make this string like like `"=CONCATENATE(RC[-43],"" ("",RC[-1],"")"")"` ? (assuming that is your problem line. – JNevill Nov 30 '17 at 19:16
  • Sorry @JNevill, that is not related to the question. I deleted that part of the code to prevent confusion. – milaske Nov 30 '17 at 19:53
  • The question pertains to this: "R6C2:R12500C2". I want to remove that part of the code because it works for 12500 rows, but I need it to be dynamic. Usually for ranges, one can turn the range into a string, and then enter the string name. That doesn't work in this format. – milaske Nov 30 '17 at 19:55
  • @milaske Excuse my ignorance, but if you read and understand it, may I ask why you're still using it? – Maldred Nov 30 '17 at 20:54

0 Answers0