replacing values in a variable number of columns

Originally Posted by mwc0914

Assume I place 5 rows of data starting in cell R1:R5. Then as I receive new data I add it to the next column, same rows (S1:S5). This continues on until I am through adding data. Assume I am finished adding data when I add data in V1:V5. So I have 5 columns of data (R thu V). Assume I keep a count of the number of columns with data in cell A1. It would equal 5 in this case.

Now, I want to place 0 in each of these cells that I have data. The coding needs to be able to support a variable number of columns because the next time I may have some other number of columns with data.

How do I code for a variable number of columns and replace the data?

Please Login or Register  to view this content.

should work for you.


note, you might need to check that R1 is not blank (empty) before using this code.
---

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.

---

Originally Posted by mwc0914

Thank you. But I am not familiar with the Cells(1,18) part. What is the significance of the 18?

I should have mentioned that after the 5 column is a static set of data that I do not want to touch. So what I am doing is inserting columns then placing the data there. I insert a variable number of columns each time I run the code. That is why I need code that can set values without defining what my end column will be, because I will not know....unless there is a way to figure out the ending column by knowing the starting column (which I will) plus the number of columns with data.

The Cells statement selects a Range as shown, it will not affect the area outside of it's range, and is less likely to err than a column-loop.

In this instance it imitates the click-drag of a range of cells on a worksheet, for which you can then imitate pressing the Del key by adding .Clear

If you are unsure then use the version that is .Select and put a msgbox before the following .Value = 0 to see the area selected.

---