# Getting the formula in this For loop to include the current column(colnum)

#### chazrab

##### Well-known Member
This code
Code:
``````Dim X As Range
Sheets("Budget").Select
For i = colnum + 1 To 54
Cells(30, i).Value = Cells(30, i - 1).Value - Cells(18, i).Value
If Cells(30, i) = 0 Then
MsgBox "Balance is 0 at col  " & i & "  " & "  and week  " & Cells(2, i)
Range(Cells(30, i + 1), Cells(30, 54)).ClearContents
Exit For
End If
Next
End Sub``````
works perfectly for every column AFTER colnum. When I change this line
Code:
`` For i = colnum + 1 To 54``
to
Code:
`` For i = colnum  To 54``
the calculation
Code:
`` Cells(30, i).Value = Cells(30, i - 1).Value - Cells(18, i).Value``
does not calculate correctly. It is something very simple I haven't been able to figure out yet.
That is, to use For i = colnum To 54 and have the formula calculate the correct balances on the current (colnum) and each successive cell column.
It's been a struggle. Can anyone help me with the correct formula that will include colnum
and not start the For loop at colnum +1 to have it come out right ?

Tried to keep it simple, but complete.

Thanks for anyone's help.
cr

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Michael M

##### Well-known Member
You haven't declared colnum ?
AND what is colnum set to in the first instance ?

#### chazrab

##### Well-known Member
You haven't declared colnum ?
AND what is colnum set to in the first instance ?

Colnum is a function in a module that varies with time. This should be self explanatory:
Code:
``````Function colnum()
colnum = Round((Date - DateValue("12/19/2018")) / 7, 0)
'MsgBox colnum
End Function``````
Very simply, as weeks change throughout the year, so does the value of colnum. To me, this has nothing to do with my
issue - the problem is in the calculation in the For loop formula I created.
cr

Replies
0
Views
41
Replies
1
Views
185
Replies
4
Views
275
Replies
2
Views
73
Replies
0
Views
74