Looking for an easier way to change Ranges

[sureng19]

This could very well be the most ignorant question ever asked in the forum. But, this is the only way to learn!!

I often have to change ranges in macros because in one sheet the Data would be in column "C", in another in "F" another sheet may have in "I", "L" & "O" where I have to rune the macro three times for that page.
As it is, for the examples below, I have to change all the references to C in the macros which can be scattered in several rows across the macro. That, "C" to "F", "C2" to "F2" and so forth.

As examples of macro ranges.

ex1) For Each r In Range("C2", Range("C" & Rows.Count).End(xlUp))....

ex2) With Range("C2", Range("C" & Rows.Count).End(xlUp)).Offset(, -2).Resize(, 2)
.......iferror(left(C2,find(""."",C2)-1),"""")......

Is there a way to define in a manner, i = "C"
i2 = "C2"
For Each r In Range("i2", Range("i" & Rows.Count).End(xlUp))....

so, here we have redefined i as C, and i2 as C2. So all I have to do is just change the definition line from i = "C", "F", "I" or "L", etc as the case may be, without having to change every "i" in the macro.
I hope I at least make sense in what I need to accomplish.

Thank you very much.

 

[sureng19]

And as an addition to post #7 method:
If you want to run a procedure multiple times with different parameters, you can create one that accepts variable input. Like this:

Sub This(ByVal i As String)
Debug.Print Range(i & 2 & ":" & i & 5).Address
End Sub

and you can call it like these:

Sub test_1()
This ("C") 'returns $C$2:$C$5
End Sub

Sub test_2()
This ("D") 'returns $D$2:$D$5
End Sub

Akuini, Thank you for the suggestions. I did not get to try on all the macros, but the method works for the few i tried. Thanks again!!

 

[sureng19]

Revised:

You need some way of identifying the column.
The below relies on the activecell, so select any cell in column C before running it.
An input box as previously suggested would be another option.

Sub EnterFormula()

    Dim iCol As Long
   
    iCol = ActiveCell.Column            ' <--- You can use and input box or in this case it is based on the active cell

   
    With Range(Cells(2, iCol), Cells(Rows.Count, iCol).End(xlUp)).Offset(, -2).Resize(, 2)
        .Resize(1).FormulaR1C1 = Array("=iferror(left(RC[2],find(""."",RC[2])-1),"""")", _
                                        "=iferror(mid(RC[2],find(""."",RC[2])+1,8),"""")")
        .FillDown
        .Value = .Value
    End With

End Sub

Alex, What you came up with is exactly what I was looking for as it is easier for me to apply to the macros with my very limited knowledge of vba. For anyone who wishes to use the code, the second part of the array needed to be modified to (RC[1] from (RC[2] as in the code below.

Sub EnterFormula()

Dim iCol As Long   
    iCol = ActiveCell.Column            ' <--- You can use and input box or in this case it is based on the active cell

    With Range(Cells(2, iCol), Cells(Rows.Count, iCol).End(xlUp)).Offset(, -2).Resize(, 2)
        .Resize(1).FormulaR1C1 = Array("=iferror(left(RC[2],find(""."",RC[2])-1),"""")", _
                                        "=iferror(mid(RC[1],find(""."",RC[1])+1,8),"""")")
        .FillDown
        .Value = .Value
    End With
End Sub

Alex, Thank you you very much!!

 

[Alex Blakenburg]

Good pick up. Thanks for the feedback and glad we could help