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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
579
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
 

Some videos you may like

Excel Facts

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
You haven't declared colnum ?
AND what is colnum set to in the first instance ?
 

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
579
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,711
Messages
5,524,436
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top