returning a variant

**mark-ainsworth** · 04-17-2011, 10:01 AM

I'm sure this is simple but i acn't work it out. I have a variant called 'lastweek' taht i want to return its value to an excel cell. The code should loop through cells B31-99 (these are dates). if any with the last 7 days then it adds the value of the equivilant cell 3 columns accross to 'lastweek'. - this isn't working tho - any idea's?

Please Login or Register  to view this content.

Thanks,
Mark

**Marcol** · 04-17-2011, 10:57 AM

This is just a guess, if it is not what you mean then post a sample workbook showing your data in Columns B & E and the expected result in N7

Please Login or Register  to view this content.

**nimrod** · 04-17-2011, 01:49 PM

Please Login or Register  to view this content.

Hell Marcol:
I could be wrong , but I think the OP wants to loop thru all cells in date range and sum the value

VBA SOLUTION

Please Login or Register  to view this content.

.
.
.

FORMULA SOLUTION

=SUMIF(B31:B99,"<=" & TODAY() -7,E31:E99)

**Marcol** · 04-17-2011, 02:38 PM

@ nimrod I saw that, but couldn't see why that would be required, there could be all sorts of dates >= to Date()-7, in any order. That will just return the last one found.

**nimrod** · 04-17-2011, 02:44 PM

lastweek.Value = lastweek.Value + c.Offset(0, 3).Value

Hi Marcol

To me this line indicates the OP is basically wanting to do a "SumIf" based on a date range i.e. take a numbers from offset 0,3 and tally them up ... oh well , the OP can decide what he/she wants from the posts ... cheers

**mark-ainsworth** · 04-18-2011, 11:17 AM

Thinkk I'm more confused now. Sorry.

This is my current code:

Please Login or Register  to view this content.

The above works fine. What i want to add in is when the 'For' loop is looping round the worksheets, i want it to do a second loop through the cells B31:B99 and look if any of the dates in this loop are in the last 7 days. If there are I want it to add the corresponding value in coulmn E to the total 'lastweek'. After moving through each of the worksheets. the total will then be written to cell N7.

FYI Column B contains dates and column E contains payments in £.

Thanks,
Mark