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.

 

[Burrgogi]

Your question is clear as mud. Please post your entire code and wrap it in VBA tags so it's easier to read.

 

[SanjayGMusafir]

I'm novice at VBA but I think you can use 2 input boxes to tell VBA values for variables i and i2

This way you can keep it dynamic as well as flexible.

Hope it helps you in some way.

 

[jolivanes]

What is the reason that it should be Column C? Or Column D?
You must have a reason the work with a particular Column.

 

[sureng19]

What is the reason that it should be Column C? Or Column D?
You must have a reason the work with a particular Column.

Well those are the columns where the data that needs manipulate reside. Is some sheets, the data is in column C and you out put the results to column A & B (or offset -1, -2). Because the data is not in a consistent column in all the sheets, the range has to be changed every time I run a code or I have to have several of the same macro with ranges corresponding to each set of data/sheet. Of course that can be easily done.

The question I raised is mostly as a learning experience. The two lines of codes I gave were just as examples of ranges I have in two macros that I often use. Here is a simple code that works when the Data is in column C. But to apply the code when the Data is in Column F, I have to change all the "C"s, and "C2"s to "F"s and "F2"s. Now, if the code has ten lines that I need to change every time, it is bit of a hassle and prone to miss a line.

Sub SplitTextString()
    With Range("c2", Range("c" & Rows.Count).End(xlUp)).Offset(, -2).Resize(, 2)
        .Formula = Array("=iferror(left(c2,find(""."",c2)-1),"""")", _
                         "=iferror(mid(c2,find(""."",c2)+1,8),"""")")
        .Value = .Value
    End With
End Sub

Thanks you.

 

[sureng19]

I'm novice at VBA but I think you can use 2 input boxes to tell VBA values for variables i and i2

This way you can keep it dynamic as well as flexible.

Hope it helps you in some way.

SunjayGMusafir, Thanks for the suggestion. That could very well be the only viable answer. I want to see if there are other approached. Thanks again!!

 

[Akuini]

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.

Maybe this:

Dim i As String
i = "C"
 For Each r In Range(i & "2", Range(i & Rows.Count).End(xlUp))

 

[sureng19]

Your question is clear as mud. Please post your entire code and wrap it in VBA tags so it's easier to read.

View attachment 112175

Hope this will give a better understanding. Here is a simple code that works when the Data is in column C. But to apply the code when the Data is in Column F, I have to change all the "C"s, and "C2"s to "F"s and "F2"s. Now, if the code has ten lines that I need to change every time, it is bit of a hassle and prone to miss a line. The Data resides in a very inconsistent manner in different sheets as I described before.

Sub test()
    With Range("c2", Range("c" & Rows.Count).End(xlUp)).Offset(, -2).Resize(, 2)
        .Formula = Array("=iferror(left(c2,find(""."",c2)-1),"""")", _
                         "=iferror(mid(c2,find(""."",c2)+1,8),"""")")
        .Value = .Value
    End With
End Sub

There're several different codes I use for various manipulations. But if I can find the work around for this code, I should be able to apply that for the others with necessary alterations. Sunjay suggested input boxes.

Thank you!!

 

[Akuini]

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

 

[Alex Blakenburg]

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