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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,827
Messages
5,855,878
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back