Subtract rows in dynamic range

[fonk]

Hi all,
Apologies for the layout as code tags are not working.
I am populating sheets from a userform with work dates for different tasks for different employees and are trying to sum the data for display in the user form, days worked for year etc. No problem with this, just gotta get the data to output. I have 5 different columns where the results of the equation will be.

I am attempting in Row C to subtract Row A from Row B in a dynamic range, below is some code I have modified to attempt this. How can I change the

LastRow = 0

so that it does not jump from

For i = 1 To LastRow

to the

End With

As it is it only looks at one row then skips. The equation is meant to look at Col B and if there is a date in there and the year is, in this case 2011, it will continue the equation on all that meet the IF statement.


Sub Sum_Dates()
Dim LastRow As Long
Dim i As Long
Dim iCol As Integer
LastRow = 0
' Sum_Dates Macro
For iCol = 1 To 4 'Columns C:D
iRow = Cells(65536, iCol).End(xlUp).Select
If iRow > LastRow Then LastRow = iRow
Next iCol

With Application.WorksheetFunction

'Place row totals in column C
ActiveCell.Offset(, -1).Select
For i = 1 To LastRow
If ActiveCell.Offset(, -1).Value <> " " And ActiveCell.Offset(, 1).Value = "2011" Then GoTo Line20
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
Next i
Line20:
End With
End Sub


If some one could give me a guide to get the equation working in column C would be much appreciated.
Regards, Dave
Using Excel 2010 Windows 7[/SIZE]

 

[cstimart]

Just in a quick glance before going to church...you're not using 'i' to select the cell(s) to be used for your calculation in the For/Next Loop...therefore, it looks like only one calculation is being made (over and over, possibly)

EDIT: I'm thinking you want to use this to determine the bottom rows?
iRow = Cells(Rows.Count, iCol).End(xlUp).Row

 

[fonk]

Thanks for the quick response cstimart, I'll have a play and see how I go.

Dave

 

[fonk]

Hi cstimart,
I didn't have any joy with the change, still jumping to the end but it did set me thinking a bit more.
I think I may have been going about this the wrong way, it would probably be a bit smarter to search the whole sheet for the year value; i.e 2011 or 2012 etc by means of the macro running from a drop down on the userform, then offset -1 Col for sum location, check offset Col -1 for data in that cell and then sum if data exists (there will be a Start Date in Col-2 but sometimes an End Date may not yet have been reached). I can then use the user form to check the appropriate columns and sum to display.
Hopefully this is a reasonable idea. Now to implement it.....
Just about to knock off for the day.
Thanks again for your assistance.
Regards, Dave

 

[cstimart]

I think you might have the basis for what you want, but instead of using Activecell, use Cells(i,1) or Range("A" & i) within the loop...also, is the year 2011 a string value or a number...if it's a number, remove the quotes from around it.

 

[fonk]

Hi cstimart,
Thanks for the input, I'll get onto it tomorrow and let you know how I go.
regards, Dave

 

[fonk]

Sub CmdSchYr_Click() 'Find code by Von Pookie
Dim c As Range, FoundCells As Range
Dim LastRow As Long
Dim firstaddress As String
Dim Col As Range
Dim rowsTot As Long
Application.ScreenUpdating = False
With ActiveSheet
    'find first cell that contains CboSelYr.Value
    Set c = .Cells.Find(What:=TbSelYr.Value, After:=.Cells(Rows.Count, 3), LookIn:=xlValues, LookAt:= _
    xlPart, MatchCase:=False)
 
    'if the search returns a cell
    If Not c Is Nothing Then
        'note the address of first cell found
        firstaddress = c.Address
        Do
            'FoundCells is the variable that will refer to all of the
            'cells that are returned in the search
            If FoundCells Is Nothing Then
                Set FoundCells = c
            Else
                Set FoundCells = Union(c, FoundCells)
            End If
 
            'find the next instance of CboSelYr.Value
            Set c = .Cells.FindNext(c)
        Loop While Not c Is Nothing And firstaddress <> c.Address
        'after entire sheet searched, select all found cells
        FoundCells.Select
            FoundCells.Offset(, -1).Select
                  FoundCells.Offset(, -1).FormulaR1C1 = "=SUM(RC[-1]-RC[-2]+1)"
           End If
 
Range("A3").Select
Cells.Columns.AutoFit
Application.ScreenUpdating = True
End With
End Sub

Hi cstimart,
Here is the code I have modified to suit my purposes, I found this thread by Von Pookie and it did exactly what I was after. I have deleted some of my additions from this to make it a bit clearer to view. It's a nice bit of code that would have taken me a long time and much angst to figure out. Thanks to Von Pookie and yourself. Happy camper now as this little project is finished (hopefully).
Regards, Dave