replacing values in a variable number of columns

Originally Posted by mwc0914

I know the number of columns. It is stored on cell A1. I need to know how to go through the columns replacing the data a column at a time. I have some code that almost works...

Column = "r"

For i = 1 To number_of_columns
Range(Column & 1).Select
ActiveCell.FormulaR1C1 = "0"
Range(Column & 1).Select
Selection.AutoFill Destination:=Range(Column & 1, Column & 5), Type:=xlFillValues
Column = Column + 1 <----- This is what needs to work
Next i

I am curious as to why you would use a loop to do something the difficult way when there is a simple single statement to perform that.

Using your 'column' and LastRow = 5

iColumn = Asc(Column) - 96
LastRow = 5
Range(Cells(1, 18), Cells(LastRow, 18 + iColumn)).Select
Selection.Value = 0

---
This will only work up to column Z, but that may suffice for your needs.

note, you could also use

Range(Cells(1, 18), Cells(LastRow, iColumn)).Clear

---
To your loop, whilst there is a method to use R+1 it gets difficult as you pass column Z, so I would avoid using that address style for what you are doing.

---