resource lookup Macro

[Jim@Tech]

I wonder if someone could suggest some cunning macro code that will make my
life a lot easier...

I have a list of tasks in column A, each task has three rows for budge,
actual & variance, and the columns to the right list all the resources
assigned to the project. Where a resource is sheduled to work on a task, the
number of hours they are budgeted is entered against that task for that
resource, as below:

Resource 1 Resource 2 Resource 3
Task 1 Budget 5 0 7
Actual
Variance
Task 2 Budget 0 0 2
Actual
Variance
Task 3 Budget 2 7 0
Actual
Variance

There are 98 tasks and 161 resources at the moment, so quite a lot of data.
What I would like to be able to do is summarise the resources against each
task in a seperate workbook. It's easy enough to set up an If statement that
copies the resource name if the Budget entry is greater than 0 for a given
task, i.e:
If(c2>0,C1,"") and copy that along the row of resources to get this:

Task 1 Resource 1 Resource 3
Task 2 Resource 3
Task 3 Resource 1 Resource 2

but this is still pretty clunky.

What I'm hoping for is a bit of code that will give me something more like
this:
Task 1 Resource 1 Resource 3
Task 2 Resource 3
Task 3 Resource 1 Resource 2



Any ideas?

Thanks
Jim.

 

[Joel]

This code assumes the data is in the 1st sheet of the workbook you are
opening and the data will be put in the 1st tab of the workbook where the
macro is located

you can change the following two lines as required

from
Set DataSht = DataBk.Sheets(1)
Set SumSheet = ThisWorkbook.Sheets(1)
to
Set DataSht = DataBk.Sheets("Sheet2")
Set SumSheet = ThisWorkbook.Sheets("Sheet3")


Sub GetResources()

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox ("Cannot open file - exiting Macro")
Exit Sub
End If

Set DataBk = Workbooks.Open(Filename:=fileToOpen)
Set DataSht = DataBk.Sheets(1)

Set SumSheet = ThisWorkbook.Sheets(1)

SumRow = 1
With DataSht
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = .Range("B" & Rows.Count).End(xlUp).Row

For RowCount = 2 To LastRow
RowType = UCase(.Range("B" & RowCount))
SumString = ""
If RowType = "BUDGET" Then
Task = .Range("A" & RowCount)
SumSheet.Range("A" & SumRow) = Task
SumCol = 2
For ColCount = 3 To LastCol
Hours = .Cells(RowCount, ColCount)
If Hours > 0 Then
Resource = .Cells(1, ColCount)
SumSheet.Cells(SumRow, SumCol) = Resource
SumCol = SumCol + 1
End If
Next ColCount
SumRow = SumRow + 1
End If
Next RowCount
DataBk.Close savechanges:=False
End With
End Sub

 

[Jim@Tech]

Thanks for the response Joel, and sorry to take so long getting back to you,
it took a while to get time to test it out!
I simplified the layout of the table below to make it easier to explain and
your code works a treat as long as I modify my actual table to match the
layout below, in reality my task names are in column E, the data type
(Budget, Actual & Variance) is in column F and actually has two Actuals
types, internal and contractor, the first resource is in column AF, the row
with resource names is row 1 but the first row of data is row 12.

I tried changing some of the parameters of your code to account for this e.g:

LastCol = .Cells(5, Columns.Count).End(xlToLeft).Column {to count columns E
to end}
LastRow = .Range("E" & Rows.Count).End(xlUp).Row {to count rows that contain
task names}

but couldn't get it working - I'd be really grateful if you could put me
right!

Also, is it possible to add some code to pull out the budget and actuals
figures along with the resource name?

Thanks a million,
Jim.

 

[Joel]

let me explain how Last and Last Row works. Range object like Range("A1")
can contain a variable for the row number

Rowcount = 5
Range("A" & RowCount)

A range address can be made up of 2 strings a column (A) and a Row Number (5
which is an integer). Excel automatically convewrt Row 5 to a string and put
the number at the end of the Column A.

Cells(Row,Column) takes a integer Row number and with a integer column
number of a column Letter.

Cells(1,5) or Cells(1,"E") which is the same as Range("E1")

In the statement below Columns.Count is the last column in the worksheet
(column IV in excel 2003). The line below say to go to the last column in
ROW 1, then move to the left until a cell with data is found and then return
the column Number. You don't have to say start at column 5.

LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

The LastRow works simillar to LastCol
Rows.Count is the l;ast Row of the wroksheet (65536 in excel 2003). The
statment say to go to the last row in column B then move up the worksheet
until a cell with data is found and return the row number.

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

 

[Jim@Tech]

Brilliant, thanks Joel! After a bit of trial and error I managed to get this
working on my real-world table so thanks for the tutorial.

How can I modify the code to also pull out the budget and actual figures
associated with the resources on each task?
I've tried to add lines under Resource = .Cells(1, ColCount) as below, but
not surprisingly this hasn't worked...

Resource = .Cells(1, colcount)
Budget = .Cells(RowCount, colcount)
Actual = .Cells(RowCount + 1, colcount)
sumsheet.Cells(sumrow, sumcol) = Resource
sumsheet.Cells(sumrow + 1, sumcol) = Budget
sumsheet.Cells(sumrow + 2, sumcol) = Actual

Sorry to take up so much of your time but could you point out where I'm
going worng here too please? (Just to add more complication, I would ideally
like to sum the two Actuals values on the sumsheet if possible).

Thank-you!

 

[Joel]

Your code is perfect. The problem is you are wrting over the Budget and
Actual amounts when you write the next task. The solution is simple

from
SumRow = SumRow + 1
to
SumRow = SumRow + 3