CODEWITHSUNDEEP
vbaexcel
Adit2789
Adit2789

Reputation: 149

Changing a cell formula based on for loop iteration

I am trying to insert and change a counta formula based on the iteration of the for loop. The problem I am facing is that even though the code runs without error, nothing happens. The totalrow variable is consistently 0. I'm not sure where I am going wrong here. All the variable have been initialized outside the loop. CurrentColname is string, countUsedCols is long, totalRows is long.

For i = 2 To countUsedCols
    ActiveSheet.Cells(3, i).Select
    currentColName = ActiveSheet.Cells(3, i).Value
    If currentColName = "Valid From" Then

        totalrow = ActiveWorkbook.Sheets("Customer_Facing_View").Range("AR1").Formula = "=COUNTA($" & i & ":$" & i & ")"

        totalrow = ActiveWorkbook.Sheets("Customer_Facing_View").Range("AR1").Value

Upvotes: 0

Views: 705

Answers (2)

Galen
Galen

Reputation: 1

I guess you would like to write a formula into Cell AR1 to calculate the number of rows (with the first totalrow statement); and get that value back (with the second totalrow statement). But I believe the first totalrow statement is wrong as Scott indicated. You need change it.

Also for the Counta formula you need to include your original worksheet name otherwise it just count the column in the Customer_Facing_View worksheet.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

A couple of things.

In vba when you put X=Y=Z X becomes a Boolean value based on whether y = z or not.

So when you write:

totalrow = ActiveWorkbook.Sheets("Customer_Facing_View").Range("AR1").Formula = "=COUNTA($" & i & ":$" & i & ")"

Totalrow is returning false because the formula in that cell is not the same as the string you have provided.

Second your COUNT A will resolve to a row not a column. i is a number and not a letter.

So remove the totalrow = from the first expression and change the range reference.

ActiveWorkbook.Sheets("Customer_Facing_View").Range("AR1").Formula = "=COUNTA(" & Columns(i).Address &")"

Upvotes: 2

Related Questions