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?

Dim lastweek As Variant

...

For Each c In ws.Range("B31:B99").Cells
                
If c.Value >= (CLng(Date) - 7) Then lastweek.Value = lastweek.Value + c.Offset(0, 3).Value _
                    
Next

Me.Range("N7").Value = lastweek.Value

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

Sub test()
    Dim lastweek As Long
    Dim c As Range
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    lastweek = 10
    
    For Each c In ws.Range("B31:B99")
        If c >= (CLng(Date) - 7) Then
            Range("N7") = lastweek + c.Offset(0, 3)
            Exit For
        End If
    Next

End Sub

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

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

Public Sub demoTwo()
   Set ws = ActiveSheet
   ws.Range("N7").Value = _
   Application.WorksheetFunction.SumIf(ws.Range("B31:B99"), "<=" & Date - 7, ws.Range("E31:E99"))
End Sub

.
.
.

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:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim c As Object
Dim lastweek As Long

Me.Range("C4:J" & Rows.Count).ClearContents
   

    For Each ws In Worksheets
        If ws.Index > Sheets("Start").Index And ws.Index < Sheets("End").Index Then
        
            Me.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value _
                    = "=HYPERLINK(" & Chr(34) & Chr(91) & ThisWorkbook.Name & Chr(93) & Chr(39) & ws.Name & Chr(39) & "!A1" & Chr(34) & Chr(44) & Chr(34) & ws.Range("C4").Value & Chr(34) & ")"
            
            If ws.Range("D8").Value <> "" Then
                
                Me.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Ex-resident"
            
            Else
                Me.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value _
                    = ws.Range("D5").Value
            
            End If

                Me.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value _
                        = ws.Range("I3").Value
                Me.Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value _
                        = ws.Range("I5").Value
                Me.Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Value _
                        = ws.Range("I7").Value
                Me.Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value _
                        = Me.Range("F" & Rows.Count).End(xlUp) / Me.Range("E" & Rows.Count).End(xlUp)
                Me.Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Value _
                        = ws.Range("N5").Value
                Me.Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Value _
                        = ws.Range("N6").Value
                                              
        End If
                                  
    Next ws
       
Beep
End Sub

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