VBA to sum a range in a different worksheet

[hakmed]

Hi,

I can usually make my VBA code work but I just can't seem to get the sum function to work properly.

I have a sheet for every person in my small business (8 people), each sheet is named after the employee. Their time gets entered into their sheets every day (so I have the date, time in, time out, and total hours (column I))

On Sheet 2 I want to be able to enter a start date (A2) and an end date (B2). I then want a VBA to search each employees sheet and sum the hours between these two dates and show me this sum on sheet2 on a specified cell

On sheet 2 I have every employee listed from A7 till A15.

for example if I enter 8-03-19 into Sheet2 A2 and 14-03-19 into sheet 2 A3, the VBA should be able to look at Sheet2 A7 (lets say that says john) then look in the sheet named john sum the total hours (column I) from 8-03-19 to 14-03-19.

I can mage to use a for loop to search johns sheet and find the row that has 8-03-14 but then getting it to sum the rows I cant manage.

Here's my code thus far:
Sub Hours()


Dim i As Integer


For i = 2 To 32


a = Worksheets("Sheet2").Range("A7") Here "a" becomes John
If Worksheets(a).Cells(i, 2).Value = Worksheets("Sheet2").Range("A2").Value Then Here im comparing sheet John's column B until i find the date thats in Sheet2 A2
Worksheets("Sheet2").Range("B7") = Worksheets(a).Cells(i, 9).Value + Worksheets(a).Cells(i + 1, 9).Value + Worksheets(a).Cells(i + 2, 9).Value + Worksheets(a).Cells(i + 3, 9).Value + Worksheets(a).Cells(i + 4, 9).Value + Worksheets(a).Cells(i + 5, 9).Value + Worksheets(a).Cells(i + 6, 9).Value This is a very crude way of summing johns column I from the start date in Sheet2 A2 for 7 days
End If


Next i


End Sub



Im trying to explain this as best as I can, if you need an explanation just ask. PS once I figure it out for one employee I can just repeat the code for every employee. At least thats what I wouldve done, unless theres a better way.

Thanks!!

 

[DanteAmor]

Try this

Sub Hours()
    Dim i As Integer
    Dim sh As Worksheet
    Dim lr As Long
    
    a = Worksheets("Sheet2").Range("A7") 'Here "a" becomes John
    Set sh = Sheets(a)
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        If sh.Cells(i, 2).Value >= Worksheets("Sheet2").Range("A2").Value And _
           sh.Cells(i, 2).Value <= Worksheets("Sheet2").Range("A3").Value Then
            wtot = wtot + sh.Cells(i, 9).Value
        End If
    Next i
    Worksheets("Sheet2").Range("B7").Value = wtot
End Sub

Let me know if you have any doubt

 

[hakmed]

Awesome thanks!!!

Im quite disappointed I didnt think of the two conditions for the if statement being >= A2 and <= A3, thats pretty obvious now that you mentioned it.

One thing, if you have the time, can you explain this line (im guessing its figuring out what the end of column B is to set that as the limit to the for loop, but can you please explain the syntax and whatnot. Super appreciated!
lr = sh.Range("B" & Rows.Count).End(xlUp).Row

 

[MARK858]

B is the column being looked at, Rows.Count is the total number of the rows in the column, End(XlUp) looks from the last row (the row number from Rows.Count) up until it finds data.
.Row is the row number of the found cell.

 

[DanteAmor]

Awesome thanks!!!

Im quite disappointed I didnt think of the two conditions for the if statement being >= A2 and <= A3, thats pretty obvious now that you mentioned it.

One thing, if you have the time, can you explain this line (im guessing its figuring out what the end of column B is to set that as the limit to the for loop, but can you please explain the syntax and whatnot. Super appreciated!
lr = sh.Range("B" & Rows.Count).End(xlUp).Row

I explain to you

lr = sh.Range("B" & Rows.Count).End(xlUp).Row

lr.- variable to store the last row with data
sh.- object of the sheet sheets(a)
Range("B" .- Reference to column "B", to obtain the last row with data from column "B"
Rows.Count.- Total number of rows of the sheet, does not say which sheet because all the sheets have the same number of rows
End(xlUp) .- Of the total of rows it crosses the position upwards and of certain form there stops the pointer of excel
Row.- Row number where the excel pointer is located.


To better understand the above, do the following. With the excel cursor, go to the last row of the excel sheet, it can be in column B, that is, put the cursor in cell B1048576. Now press the End key, now press the up arrow key (End(xlUp)), you will see that the cursor is positioned in the last cell with data from column "B"

Let me know if you have any doubt.