Stop and read this entire post, as if you just cut and paste, it will wreck your current sheet, altering cells! DEPLOY AT YOUR RISK, OR DEPLOY on A COPY!
Requirements:
In the function CalcRow, make certain that the pCell(payment cell) is the correct Column.
In the function CalcRow, make certain that the bCell(balance cell) is the correct Column.
In the function CalcRow, make certain that the cCell(calculated cell) is the correct Column.
The below code starts by running StartCalc.
StartCalc loads a vriable called SheetNumMax with a call to the function counttabs. Count tabs counts and returns the number of sheets in the workbook.
StartCalc then loops through each sheet, and calls a function named FindLastRow, to see how many rows there are on the current sheet.
FindLastRow initializes a variable of rownum, and we loop though each row with the variable "t".
During the loop, we call CalcRow, to perform the claculations of Outstanding balances.
CalcRow also calls for checking the value of cCell, by calling CheckVal and passing cCell.
CheckVal looks at the value of cCell, and if it is greater than 0, it colors the cell yellow.
Function StartCalc()
Dim SheetNumMax
Dim rownum
SheetNumMax = counttabs
For st = 1 To SheetNumMax
ThisWorkbook.Worksheets(st).Activate
rownum = FindLastRow(st)
For t = 2 To rownum
CalcRow (t)
Next
Next
End Function
Function counttabs()
Dim numTabs
numTabs = ThisWorkbook.Worksheets.Count
counttabs = numTabs
End Function
Function FindLastRow(OnWhatsheet)
FindLastRow = Cells(ThisWorkbook.Worksheets(OnWhatsheet).Rows.Count, 1).End(xlUp).Row
End Function
Function CalcRow(whatrow)
Dim bCell, pCell, cCell
pCell = "A" & whatrow
bCell = "B" & whatrow
cCell = "C" & whatrow
theval = Range(bCell).Value - Range(pCell).Value
MsgBox (theval) 'remark out this line if you do not wish to have feedback of totals!
Range(cCell).Value = theval
CheckVal (cCell)
End Function
Sub CheckVal(whatcell)
If Range(whatcell).Value > 0 Then
Range(whatcell).Interior.ColorIndex = 6 'yellow
End If
End Sub
Unfortunately, I may have overstated/implied my vba ability.
I think I understand what it is that the vba coding requires to work, but I am not knowledgeable enough to code this and know at which point to insert into the coding that already exists (which I also got from the internet).
I would really appreciate if you could provide the coding and then and I can step through it to see how it works and the manner in which it is constructed.
And absolutely, I do want to learn it, but lets not walk before I can crawl :-)
Merci
Michael
Based on this I introduced the following code at the start of my vba, again got on the internet, and having tested it in my file it seems to be working okay. Not sure if it covers everything you were suggesting.
Sub kl()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
'be aware as this might produce an error, if Shet "name" does not exist
Set ws = wb.Sheets("name")
' if wb is other than the active workbook
wb.Activate
ws.Select
End Sub
Thanks again for your guidance.
Can anyone advise what may be missing from my vba coding to prevent having to do this and manually re-apply the formula monthly.
Thankyou