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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
712
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
Joined
Oct 27, 2005
Messages
21,311
Office Version
  1. 365
  2. 2007
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
712
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top