from
Worksheets("BuildingList").Range("B:B").Find(strBuilding)
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(strBuilding)
> 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
|
Similar Threads
