simple (I think?) question about assigning a constant to represent a column

[kbishop94]

I apologize for asking such a remedial and simple question, but I couldn’t really find the exact answer using google and its only making me more confused lol

Throughout my code in the spreadsheet I am using the furthest out column that is being used on the spreadsheet (column “IR”) in my code… example:

For Each cell In Range("C4:IR4").Cells

The problem is, when a situation arises where I’ll have to insert a new column (which won’t be very often, but it will eventually happen) I’ll have to go back in to the code and manually change all the “IR4” ‘s to “IS4” and so on… Now the “C” column will not ever change, and the row (4) will not ever change… Only the “IR” column will. Now, I understand the concept(kinda) on assigning a CONSTANT to represent the farthest out column (IR in this case) that is being utilized in the spreadsheet, but what I do not know is how to actually put that into code along with exactly how the code is written every time “IR” is referenced throughout all the code. ?? Thanks

 

[mumps]

Try:

Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    For Each cell In Range(Cells(4, 3), Cells(4, lCol))

 

[kbishop94]

Try:

Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    For Each cell In Range(Cells(4, 3), Cells(4, lCol))

I apologize but I think I am not entering it correctly. I am getting the error " Invalid Outside Procedure" (?)

 

[Joe4]

I apologize but I think I am not entering it correctly. I am getting the error " Invalid Outside Procedure" (?)

What mumps gave you was just past of code to replace the one line you had:

[COLOR=#333333]For Each cell In Range("C4:IR4").Cells[/COLOR]

Did you place his code within a Sub Procedure (between a "Sub ..." and "End Sub")?
That error message is something I would expect if you just pasted that section of code at random in the VB Editor and not within a Sub Procedure.

 

[kbishop94]

Thank you. I think I got it.