VBA to sum a range in a different worksheet

hakmed

New Member
Joined
Mar 15, 2019
Messages
2
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!!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,789
Office Version
  1. 2010
Platform
  1. Windows
Try this

Code:
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

New Member
Joined
Mar 15, 2019
Messages
2
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,460
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,789
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,948
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top