PDA

View Full Version : Sum Values in defined target



vvSTRIDEvv
02-04-2011, 07:21 AM
I have a workbook that essentially looks through a bunch of data sheets, to ultimately return a given value within a given date range.

Within a userform the user will enter a "from date" and a "to date" along with some other criteria, and I have a hidden text box in the form, which determines the number of days "days.text".

I am able to determine the target cell of the "criteria" and "To Date". Let's say the target is (20, 8). The value of "days.text" is 5 based on the "From date". So I'll need to sum (15, 8) + (16, 8) + (17, 8) + (18, 8) + (19, 8) + (20, 8).

Here is the order of things:
Listbox - Select Sheet
DatePicker - From Date
DatePicker2 - To Date
Listbox2 - Criteria
days.text - Shows number of days we'll say it's "5"

I'm able to find the "Sheet" and target of "DatePicker2 (Row)" and "Criteria (Column)"

How can I tell VB to: with target offset by days.text "5", Sum to target..

mbarron
02-04-2011, 09:32 PM
Something like this:
Application.WorksheetFunction.Sum(Cells(20, 1).Offset(-5).Resize(6, 1))

vvSTRIDEvv
02-05-2011, 09:24 PM
Works perfect, thanks!!
Result Dbtarget = Application.WorksheetFunction.Sum(target.Offset(-nod.value).Resize(nod.value + 1))

shrivallabha
02-06-2011, 03:25 AM
You can shorten:


Dbtarget = Application.WorksheetFunction.Sum(target.Offset(-nod.value).Resize(nod.value + 1))


To:


Dbtarget = Application.Sum(target.Offset(-nod.value).Resize(nod.value + 1))