calculating value ebased on terms

[next]

I have a spreadsheet where i need to get total $ value based on terms, i've been strugglin for a few days now trying to write a macro that will do that for me .

Here's how it should work:
For instance account AMT, second row on my spreadsheet has terms 90, i want my macro to grab total value from the leftmost column("L2") and subtract values in each column from "C2" up to "I2". That leaves me with $600 total, but only $100 due.
Same procedure should be repeated for each account.
Cell with value "2%,10,30" is the same as "net 30 days", "2%,15 60" is the same as "net 60 days".
My last idea is to write all values in 2 dimentional array and then use some expression to get desired value, but it's not working out for me. Please help.
VBA that i have:
[vba]
Option Explicit
Sub Test()
Dim x As Range, i As Integer, j As Integer
Dim LastCellb As Integer, LastCellL As Integer
Dim Cols As Variant, Rows As Variant
Dim iData() As Variant


LastCellb = Worksheets("CW").Range("A1").End(xlDown).Row
LastCellL = Worksheets("CW").Range("A1").End(xlToRight).Column
ReDim iData(1 To LastCellb, 1 To LastCellL) As Variant

For i = 1 To LastCellb
For j = 1 To LastCellL
iData(Rows, Cols) = i & "-" & j & ":" & Worksheets("CW").Range(j).Value
Debug.Print iData(Rows, Cols)
Next j
Next i

End Sub
[/vba]
It returns an error though, don't know how to fix it.

file is attached to this message.
Thanks for all your help.

[mdmackillop]

I would suggest a UDF (user defined function) such as
[vba]
Function Overdue(Data As Range)
Dim OD As Long, Cel As Range, c As Range
For Each Cel In Data
Set c = Cells(Cel.Row, 2)
Select Case --Right(c, 2)
Case 15
OD = OD + (Cel - c.Offset(, 1))
Case 25
OD = OD + (Cel - Application.Sum(c.Offset(, 1).Resize(, 3)))
Case 30
OD = OD + (Cel - Application.Sum(c.Offset(, 1).Resize(, 4)))
Case 45
OD = OD + (Cel - Application.Sum(c.Offset(, 1).Resize(, 5)))
Case 60
OD = OD + (Cel - Application.Sum(c.Offset(, 1).Resize(, 6)))
Case 90
OD = OD + (Cel - Application.Sum(c.Offset(, 1).Resize(, 7)))
Case 120
OD = OD + (Cel - Application.Sum(c.Offset(, 1).Resize(, 8)))
End Select
Next
Overdue = OD
End Function

[/vba]
This might need a bit of fine tuning.
I don't see how you can have a 20 day term without a corresponding 20 day end period.

[next]

a Function, great! That will clean up my major formatting macro a bit.
I cleaned up arrays a bit, now it's working, but your way looks cleaner =).

I don't see how you can have a 20 day term without a corresponding 20 day end period.

I know, kind of silly, but to answer your question, it automatically transforms to 25 days.

Thanks for the help.

[next]

It's not working for some reason. "Data" is the "Terms" column right?
Few questions:
[vba]Select Case --Right(c, 2)[/vba] What does [vba]--Right(c, 2)[/vba] do?
How is [vba]Application.Sum(c.Offset(, 1).Resize(, 3))[/vba] working?

Built in help file (search) is so useless, omg, i can't find anything in there.
Any suggestions on good "vba reference" file?

This seems to work:
[vba]
Option Explicit
Public Function Due(Col As Range)
Dim x As Range
Dim LastCellb As Integer, LastCellL As Integer, Total As Integer

LastCellb = Worksheets("CW").Range("A1").End(xlDown).Row

For Each x In Col
If x.Row > LastCellb Then Exit For
Select Case x.Value
Case 0 To 15: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "D" & x.Row))
Case 16 To 25: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "E" & x.Row))
Case 26 To 30: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "F" & x.Row))
Case 31 To 45: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "H" & x.Row))
Case 46 To 90: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "J" & x.Row))
Case "2%,10,30": Total = Total + Application.Sum(Range("K" & x.Row & ":" & "F" & x.Row))
Case "2%,15 60": Total = Total + Application.Sum(Range("K" & x.Row & ":" & "H" & x.Row))
End Select
Next
Due = Total
End Function
[/vba]

Another Question:
I have almost the same spreadsheet saved on a shared drive, the difference is that each account has comments written in Column M, could you possibly make this book(labRat.xls) search that file for comments associated with each account and write same comments to column M, basicaly how do i sinchronize two spreadsheets?

[mdmackillop]

Do you have VBA Help installed? It is accessed from the VB Editor area.
I'll get back later on your questions.

[next]

Yeah, i do have it, the problem is that i can never find what i'm looking for in it.

[next]

Nomatter what i do i doesn't detect:
2%,10, 30 and 2%, 15 60
How do i make it see those values?
[VBA]
Option Explicit
Public Function Due(Col As Range)
Dim x As Range
Dim LastCellb As Integer, LastCellL As Integer, Total As Integer

LastCellb = Worksheets("CW").Range("A1").End(xlDown).Row

For Each x In Col
If x.Row > LastCellb Then Exit For

Select Case x.Value
Case 0 To 15: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "D" & x.Row))
Case 16 To 25: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "E" & x.Row))
Case 26 To 30: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "F" & x.Row))
Case 31 To 45: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "H" & x.Row))
Case 46 To 60: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "H" & x.Row))
Case 61 To 90: Total = Total + Application.Sum(Range("K" & x.Row & ":" & "J" & x.Row))
Case "2%,10, 30": Total = Total + Application.Sum(Range("K" & x.Row & ":" & "F" & x.Row))
Case "2%, 15 60": Total = Total + Application.Sum(Range("K" & x.Row & ":" & "H" & x.Row))
End Select
Next
Due = Total
End Function
[/VBA]

[mdmackillop]

[VBA]Function Overdue(Data As Range)
Dim OD As Long, Cel As Range, c As Range
For Each Cel In Data
Set c = Cells(Cel.Row, 2)
Select Case --(Right(Trim(c), 2))
Case Is <= 15
OD = OD + (Application.Sum(c.Offset(, 2).Resize(, 8)))
Case Is <= 25
OD = OD + (Application.Sum(c.Offset(, 3).Resize(, 7)))
Case Is <= 30
OD = OD + (Application.Sum(c.Offset(, 4).Resize(, 6)))
Case Is <= 45
OD = OD + (Application.Sum(c.Offset(, 6).Resize(, 4)))
Case Is <= 60
OD = OD + (Application.Sum(c.Offset(, 7).Resize(, 3)))
Case Is <= 90
OD = OD + (Application.Sum(c.Offset(, 8).Resize(, 2)))
End Select
Next
Overdue = OD
End Function
[/VBA]

[mdmackillop]

The problem with 2%, 15, 60 etc. is that it must be exact. In yout sample, there is a space after the 30 in 2%, 10 30, and commas are not consistent.
Resize is easier to adjust than full addresses in the range, once you get the hang of it.

[next]

Thanks.
[VBA]
Case Else
If InStr(x.Value, "2%,10, 30") Then
Total = Total + Application.Sum(Range("K" & x.Row & ":" & "F" & x.Row))
ElseIf InStr(x.Value, "2%, 15 60") Then
Total = Total + Application.Sum(Range("K" & x.Row & ":" & "I" & x.Row))
[/VBA]
This seems to detect these cells.

I'm trying to figure out how to sinchronize comments between 2 spreadsheets based on account, but no luck, any ideas on how do i do that?

[mdmackillop]

[VBA]Private Sub Workbook_Open()
Dim Share As Workbook, WB As Workbook, shRange As Range
Set WB = ThisWorkbook
'Open shared workbook; set range to search
Set Share = Workbooks.Open("C:\AAA\labratmd2.xls")
Set shRange = Share.Sheets("CW").Columns(1)
'Loop through accounts; find data in shared workbook and copy to worksheet
With WB.Sheets("CW")
For Each Cel In Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
Cel.Offset(, 12) = shRange.Find(Cel).Offset(, 12)
Next
End With
'Close shared workbook
Share.Close False
End Sub
[/VBA]

BTW, your Case Else add should not be required. You should consider Data Validation to avoid data entry errors in your Terms column.

[next]

Thanks!
What do you mean by "Data Validation"?

[mdmackillop]

See sample

[next]

I get terms from database export, so this is not going to work in this case, but this is a very good idea, i will definitely use that for some other project !

[mdmackillop]

Database info often needs "cleaning" to be used in Excel. Trim is a useful function to get rid of extraneous spaces, but may not suffice in all cases (chr 160 for example). Inconsistent data can be corrected (within reason) but we need all the relevant info in order to assist.