Complex Summation
 

Hello

I have a workbook where the first worksheet contains building information
(i.e. square footage). Every worksheet after that is specific to an
improvement that can be made to a building such as Air Handlers would be one
worksheet and under that we list all the Air Handler work that has been done
on all the projects. I use a cross-refererence to get information such as
cost/sf and air handlers per square foot for each project. The last sheet
I'm going to use as a random total, where a user can enter several criteria
options and display the results in a cell.

Now to the question. Let's say Joe does work on Air Handlers for Building
A, B, and C. How do I write a macro that looks up Joe (on
Worksheets("Total").Range("A1")), then look up in the Air Handlers worksheet
which buildings Joe has done Air Handler work on, then use that information
to add all the square footage from the Building Information worksheet. Then
place it in Worksheets("Total").Range("B1").

Hope that makes sense
--
Brian

Complex Summation
 

There are a numbr a ways of doing this using filters. Below is a more
straight foreward approach.

You probably need to make the following changes

1) The Rows where the data starts. I used Row 1
2) The names of the worksheet. I just picked names.
3) The correct columns for the data. I used columns A and B.



Sub GetTotals()

BuildingTotal = 0
With Sheets("Total")
Employee = .Range("A1")
End With
With Sheets("Air Handlers")
AirRowCount = 1
Do While .Range("A" & AirRowCount) < ""
Worker = .Range("A" & AirRowCount)
If Worker = Employee Then
Building = .Range("B" & AirRowCount)
With Sheets("Building Information")
BuildRowCount = 1
Do While .Range("A" & BuildRowCount) < ""
BuildingName = .Range("A" & BuildRowCount)
If BuildingName = Building Then
BuildingTotal = BuildingTotal + _
.Range("B" & BuildRowCount)
End If
BuildRowCount = BuildRowCount + 1
Loop
End With
End If
AirRowCount = AirRowCount + 1
Loop

End With
Worksheets("Total").Range("B1") = BuildingTotal

End Sub


"Brian" wrote:

Hello

I have a workbook where the first worksheet contains building information
(i.e. square footage). Every worksheet after that is specific to an
improvement that can be made to a building such as Air Handlers would be one
worksheet and under that we list all the Air Handler work that has been done
on all the projects. I use a cross-refererence to get information such as
cost/sf and air handlers per square foot for each project. The last sheet
I'm going to use as a random total, where a user can enter several criteria
options and display the results in a cell.

Now to the question. Let's say Joe does work on Air Handlers for Building
A, B, and C. How do I write a macro that looks up Joe (on
Worksheets("Total").Range("A1")), then look up in the Air Handlers worksheet
which buildings Joe has done Air Handler work on, then use that information
to add all the square footage from the Building Information worksheet. Then
place it in Worksheets("Total").Range("B1").

Hope that makes sense
--
Brian

Complex Summation
 

Thanks for the help Joel, but I started working more at it and I got:


Dim lResult As Long, strManager As String, strBuilding As String, lsqft As
Long
Dim rFind As Range

strManager = Worksheets("Total").Range("C6").Value

lResult = 0

For count1 = 2 To 17
If Worksheets("Total").Range("C8").Value = "QTY" Then
For count2 = 3 To 503
If Worksheets(count1).Range("B" & count2).Value = strManager
Then
lResult = lResult + Worksheets(count1).Cells(count2,
5).Value
End If
Next count2
End If

If Worksheets("Total").Range("C8").Value = "Square Feet" Then
For count3 = 3 To 503
If Worksheets(count1).Range("B" & count3).Value = strManager
Then
strBuilding = Worksheets(count1).Range("C" & count3).Value
Set rFind =
Worksheets("BuildingList").Range("B:B").Find(strBu ilding)
lResult = lResult + Worksheets("BuildingList").Range("D"
& rFind.Row).Value
End If
Next count3
End If
Next count1

Calculate:
Worksheets("Total").Range("H11").Value = lResult


End Sub


The one problem that I'm running into is that lets say I have a building
named "House" and one named "Warehouse," if the guy did both of them and
"Warehouse" is above "House" then "Warehouse" data is returned twice and
"House" is not returned. How do I get specific values?

Thanks
--
Brian


"Joel" wrote:

There are a numbr a ways of doing this using filters. Below is a more
straight foreward approach.

You probably need to make the following changes

1) The Rows where the data starts. I used Row 1
2) The names of the worksheet. I just picked names.
3) The correct columns for the data. I used columns A and B.



Sub GetTotals()

BuildingTotal = 0
With Sheets("Total")
Employee = .Range("A1")
End With
With Sheets("Air Handlers")
AirRowCount = 1
Do While .Range("A" & AirRowCount) < ""
Worker = .Range("A" & AirRowCount)
If Worker = Employee Then
Building = .Range("B" & AirRowCount)
With Sheets("Building Information")
BuildRowCount = 1
Do While .Range("A" & BuildRowCount) < ""
BuildingName = .Range("A" & BuildRowCount)
If BuildingName = Building Then
BuildingTotal = BuildingTotal + _
.Range("B" & BuildRowCount)
End If
BuildRowCount = BuildRowCount + 1
Loop
End With
End If
AirRowCount = AirRowCount + 1
Loop

End With
Worksheets("Total").Range("B1") = BuildingTotal

End Sub


"Brian" wrote:

Hello

I have a workbook where the first worksheet contains building information
(i.e. square footage). Every worksheet after that is specific to an
improvement that can be made to a building such as Air Handlers would be one
worksheet and under that we list all the Air Handler work that has been done
on all the projects. I use a cross-refererence to get information such as
cost/sf and air handlers per square foot for each project. The last sheet
I'm going to use as a random total, where a user can enter several criteria
options and display the results in a cell.

Now to the question. Let's say Joe does work on Air Handlers for Building
A, B, and C. How do I write a macro that looks up Joe (on
Worksheets("Total").Range("A1")), then look up in the Air Handlers worksheet
which buildings Joe has done Air Handler work on, then use that information
to add all the square footage from the Building Information worksheet. Then
place it in Worksheets("Total").Range("B1").

Hope that makes sense
--
Brian

Complex Summation
 

from
Worksheets("BuildingList").Range("B:B").Find(strBu ilding)
to
Worksheets("BuildingList").Range("B:B").Find(what: =strBuilding, _
lookin:=xlvalues,lookat:=xlwhole)


"Brian" wrote:

Thanks for the help Joel, but I started working more at it and I got:


Dim lResult As Long, strManager As String, strBuilding As String, lsqft As
Long
Dim rFind As Range

strManager = Worksheets("Total").Range("C6").Value

lResult = 0

For count1 = 2 To 17
If Worksheets("Total").Range("C8").Value = "QTY" Then
For count2 = 3 To 503
If Worksheets(count1).Range("B" & count2).Value = strManager
Then
lResult = lResult + Worksheets(count1).Cells(count2,
5).Value
End If
Next count2
End If

If Worksheets("Total").Range("C8").Value = "Square Feet" Then
For count3 = 3 To 503
If Worksheets(count1).Range("B" & count3).Value = strManager
Then
strBuilding = Worksheets(count1).Range("C" & count3).Value
Set rFind =
Worksheets("BuildingList").Range("B:B").Find(strBu ilding)
lResult = lResult + Worksheets("BuildingList").Range("D"
& rFind.Row).Value
End If
Next count3
End If
Next count1

Calculate:
Worksheets("Total").Range("H11").Value = lResult


End Sub


The one problem that I'm running into is that lets say I have a building
named "House" and one named "Warehouse," if the guy did both of them and
"Warehouse" is above "House" then "Warehouse" data is returned twice and
"House" is not returned. How do I get specific values?

Thanks
--
Brian


"Joel" wrote:

There are a numbr a ways of doing this using filters. Below is a more
straight foreward approach.

You probably need to make the following changes

1) The Rows where the data starts. I used Row 1
2) The names of the worksheet. I just picked names.
3) The correct columns for the data. I used columns A and B.



Sub GetTotals()

BuildingTotal = 0
With Sheets("Total")
Employee = .Range("A1")
End With
With Sheets("Air Handlers")
AirRowCount = 1
Do While .Range("A" & AirRowCount) < ""
Worker = .Range("A" & AirRowCount)
If Worker = Employee Then
Building = .Range("B" & AirRowCount)
With Sheets("Building Information")
BuildRowCount = 1
Do While .Range("A" & BuildRowCount) < ""
BuildingName = .Range("A" & BuildRowCount)
If BuildingName = Building Then
BuildingTotal = BuildingTotal + _
.Range("B" & BuildRowCount)
End If
BuildRowCount = BuildRowCount + 1
Loop
End With
End If
AirRowCount = AirRowCount + 1
Loop

End With
Worksheets("Total").Range("B1") = BuildingTotal

End Sub


"Brian" wrote:

Hello

I have a workbook where the first worksheet contains building information
(i.e. square footage). Every worksheet after that is specific to an
improvement that can be made to a building such as Air Handlers would be one
worksheet and under that we list all the Air Handler work that has been done
on all the projects. I use a cross-refererence to get information such as
cost/sf and air handlers per square foot for each project. The last sheet
I'm going to use as a random total, where a user can enter several criteria
options and display the results in a cell.

Now to the question. Let's say Joe does work on Air Handlers for Building
A, B, and C. How do I write a macro that looks up Joe (on
Worksheets("Total").Range("A1")), then look up in the Air Handlers worksheet
which buildings Joe has done Air Handler work on, then use that information
to add all the square footage from the Building Information worksheet. Then
place it in Worksheets("Total").Range("B1").

Hope that makes sense
--
Brian