1

My Excel Sheet Example

The purpose of my worksheet is to input client information into the required cells like the image I posted above. Every new day, the DateCounter column will increase by one.

DateCounter formula : =IFERROR(IF(ISBLANK(B3),"",TODAY()-B3),"") (Today's date - B3 the date the row was created = how many days have passed since the row was created.)

After it's increased by one automatically, I want the Interest column to update automatically and make it equal to itself + the Per Diem of that row. (Exemple : [I3 Interest = I3 Interest + H3 Per Diem].

I have a VBA code that does exactly this but it only works when I change the DateCounter cell manually and not when the formula gets triggered automatically.

VBA CODE :

'*If "day" range is changed, update Interest cell*'

Private Sub Worksheet_Change(ByVal target As Range)

    If Not Intersect(target, Range("N3:N400")) Is Nothing Then

        Range("I" & target.Row).Value = Range("I" & target.Row).Value + Range("H" & target.Row)

        'Change Interest cell to the accounting format
        Range("I" & target.Row).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    End If
End Sub

I tried to change the code into the Worksheet_Calculate() event but It triggers every row in the column and my excel sheet crashes because of an infinite loop. I tried the example [here] . I also tried other examples but my knowledge of VBA is limited and I can't seem to get this task to work.

Crypt0x0a
  • 13
  • 6

1 Answers1

2

Formula changes do not trigger Worksheet_Change. You need Worksheet_Calculate and a static var. 

Option Explicit

Private Sub Worksheet_Calculate()
    Static tday As Long

    If Date <> tday Then
        tday = Date
        'suspend calculation so you don't run on top of yourself
         application.calculation = xlcalculationmanual

        'do all the update work here
        range(cells(2, "H"), cells(rows.count, "H").end(xlup)).copy
        cells(2, "I").pastespecial Paste:=xlPasteValues, Operation:=xlAdd


        'resume automatic calculation
         application.calculation = xlcalculationautomatic
    End If
End Sub
  • This is great but I think it only works with 1 row unfortunately. I have more than 400 rows that needs to update every day i open my sheet. One more thing, if I declare the static variable as the date, if I close my sheet, will it stay? – Crypt0x0a Jul 12 '18 at 20:19
  • a) If the date changes, then it changes for everything. You don't have to make 400 individual checks to determine if the date changes. b) Put all your updating code where it says `'do all the update work here`. –  Jul 12 '18 at 20:23
  • Okay I understand now. Now when a new day starts, every cell in the Interest column needs to update with a calculation. Is there a better way than creating a loop? – Crypt0x0a Jul 12 '18 at 21:01
  • Possibly Copy H then PasteSpecial I operation:=xlAdd or something like that. –  Jul 12 '18 at 21:03
  • Hmm, I'm not really sure how to do this due to my lack of VBA knowledge. Since the I Range is different for every client, I'd need to increment different values. Would using pasteSpecial work? – Crypt0x0a Jul 12 '18 at 21:34
  • I didn't notice the edit. It works thank you! If you don't mind, even if it works I don't understand how your edit works. could you explain it in detail? Thanks again – Crypt0x0a Jul 12 '18 at 21:48
  • I see your edit but I don't understand how it works exactly. I haven't learned about it yet. I'll try to find how your code works – Crypt0x0a Jul 12 '18 at 22:01
  • Put 10 in a cell and 50 in another. Go back and copy the 10. Right-click the cell with 50 and choose Paste Special. When the Paste Special dialog opens, have a good look at all the options available. –  Jul 12 '18 at 22:05
  • Great I understand, Thanks! – Crypt0x0a Jul 12 '18 at 22:11