new to VBA programming

[slabes]

I am trying to find all matches in a common column across multiple
worksheets and display the concatenated values of project names from
all matches in a single cell. I have been beating my head against a
wall trying to do this with array formulas to no avail.

THis is exactly what I am trying to do:

I have project schedules for several clients each on their own sheet.
Each schedule has between 1 and 25 rows (one for each project) with
columns for deadline dates for each stage of the project (copy
approval, design approval, print). On a separate sheet I have the
dreaded Excel calendar, and I would like to put a formula or UDF in
each cell that looks through each sheet for print dealine for the given
day displaying each project name that matches the date for the project
stage. Ideally the function would take a date and a project stage as
parameters and return a list of project names.

I have some programming experience with Javascript, Actionscript, and
PHP, but not with VBA.

Is this possible?

Yes, I know I should be using Microsoft Project (or similar) but that's
not an option.

Thougths? Guidance?

Michael

 

[Dave Peterson]

I'm not sure I'd use a UDF for this kind of thing. The problem I see is that if
you change the values in one of the sheets, then the UDF has to know about what
you changed--so that it can recalculate.

For the UDF to know about this, you'd have to pass it the ranges that could
change:

=myprojlist(a1,b1,sheet2!a:e,sheet3!a:3,...)
(a1 contains a date and b1 contains some header (project stage).)

for all the sheets that you want to retrieve data from--and include the ranges
that could change.

This seems impractical to me.

The alternative if you still want to keep it a UDF is to make that function
volatile--it would recalculate each time excel recalculates. Depending on how
many sheets there are and how many formulas there are, it could be noticeable.

And worse yet, if you change any part of the data (date or project) and excel
hasn't recalculated, then the results can't be trusted. You'll want to force a
recalculation before you trust the results.

But if you want to try...

Option Explicit
Function myProjList(myDate As Date, myCate As String) As String

Application.Volatile True

Dim wks As Worksheet
Dim myWkbk As Workbook
Dim myCateCol As Variant
Dim myDateCol As String
Dim myCateRow As Long
Dim iRow As Long
Dim LastRow As Long
Dim myStr As String
Dim FormulaWksName As String

'dates in column A and categories in row 1?
myDateCol = "A"
myCateRow = 1

If TypeName(Application.Caller) = "Range" Then
Set myWkbk = Application.Caller.Parent.Parent
FormulaWksName = Application.Caller.Parent.Name
Else
Set myWkbk = ActiveWorkbook
FormulaWksName = ActiveSheet.Name
End If

For Each wks In myWkbk.Worksheets
With wks
Select Case LCase(.Name)
'skip some sheets--the one with the formula
Case Is = LCase(FormulaWksName)
'do nothing
'skip any other sheets???
Case Is = "sheet1", "summary"
'do nothing
Case Else
myCateCol = Application.Match(myCate, .Rows(myCateRow), 0)
Debug.Print myCateCol
If IsError(myCateCol) Then
'no header match in this worksheet
Else
LastRow = .Cells(.Rows.Count, myDateCol).End(xlUp).Row
For iRow = myCateRow + 1 To LastRow
If IsNumeric(.Cells(iRow, myDateCol).Value) Then
If CLng(.Cells(iRow, myDateCol).Value) _
= CLng(myDate) Then
'found a match
myStr = myStr & vbLf _
& .Cells(iRow, myCateCol).Value
End If
End If
Next iRow
End If
End Select
End With
Next wks

If myStr = "" Then
'do nothing
'or
myStr = "Nothing Found!"
Else
myStr = Mid(myStr, 2)
End If

myProjList = myStr

End Function

This puts alt-enters between each line. Change this if you want. You'll have
to format the cells with the formulas to wrap text and maybe even adjust the
rowheight after a recalc.

Me, I'd use the same kind of code, but run it on demand--make it a Sub and plop
a button from the Forms toolbar on the worksheet that receives the data.

Then refresh the data only when you want.

Say your dates/categories are in A2:B99, you could use a macro like:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
myCell.Offset(0, 2).Value _
= myProjList(myCell.Value, myCell.Offset(0, 1).Value)
Next myCell

End Sub

The subroutine still depends on the function to do all the work.

 

[slabes]

This is incredible. THanks for the help the code the explanation. One
more wuestion, I would like the parameter myCate to determine which
column the dates come out of. Can that be done via a looku pbefore the
long with loop?

Michael

 

[Dave Peterson]

I'm not sure I quite understand. But...

If you have a list of categories to date columns, you could do it in code.

This line
will have to change.

Say your categories/column cross reference was in A1:Bxx of a worksheet named
"CateDateCol"

You could use:

Dim myDateCol as Variant 'notice the change!

with worksheets("Catedatecol")
mydatecol = application.vlookup(mycate,.range("a:b"),2,false)
if iserror(mydatecol) then
'just stop!
msgbox "no column found"
exit function
end if
end with

So change the declaration of myDateCol to Variant
change this one line:
mydatecol = "A"
to that block of code.

build a nice table in a new worksheet.

And add that worksheet to the list of names that should be ignored.
hey, it might even work!!!

 

[Dave Peterson]

Change this line:

msgbox "no column found"
to
myProjList = "No Column Found For Category"

I'd hate to see a bunch of message boxes for each typo I made.

 

[slabes]

What I'm trying to get at is the parameter myCate should be the
dateColumn that the function looks in to match the myDate parameter
then return the project name. Make sense? I'm not sure I'm being
clear.

 

[Dave Peterson]

It sounds like you could just swap those variables around.

I renamed them and this might work (untested, uncompiled, and doesn't include
the vlookup() stuff to get the column with the projects.

I'd try it after changing the basics--worksheet names, Project column is A?s,
rows

Option Explicit
Function myProjList(myDate As Date, myCate As String) As String

Application.Volatile True

Dim wks As Worksheet
Dim myWkbk As Workbook
Dim myProjCol As Variant
Dim myCateCol As String
Dim myCateRow As Long
Dim iRow As Long
Dim LastRow As Long
Dim myStr As String
Dim FormulaWksName As String

'dates in column A and categories in row 1?
myProjCol = "A"
myCateRow = 1

If TypeName(Application.Caller) = "Range" Then
Set myWkbk = Application.Caller.Parent.Parent
FormulaWksName = Application.Caller.Parent.Name
Else
Set myWkbk = ActiveWorkbook
FormulaWksName = ActiveSheet.Name
End If

For Each wks In myWkbk.Worksheets
With wks
Select Case LCase(.Name)
'skip some sheets--the one with the formula
Case Is = LCase(FormulaWksName)
'do nothing
'skip any other sheets???
Case Is = "sheet1", "summary"
'do nothing
Case Else
myCateCol = Application.Match(myCate, .Rows(myCateRow), 0)
'Debug.Print myProjCol
If IsError(myCateCol) Then
'no header match in this worksheet
Else
LastRow = .Cells(.Rows.Count, myCateCol).End(xlUp).Row
For iRow = myCateRow + 1 To LastRow
If IsNumeric(.Cells(iRow, myCateCol).Value) Then
If CLng(.Cells(iRow, myCateCol).Value) _
= CLng(myDate) Then
'found a match
myStr = myStr & vbLf _
& .Cells(iRow, myProjCol).Value
End If
End If
Next iRow
End If
End Select
End With
Next wks

If myStr = "" Then
'do nothing
'or
myStr = "Nothing Found!"
Else
myStr = Mid(myStr, 2)
End If

myProjList = myStr

End Function

What I'm trying to get at is the parameter myCate should be the
dateColumn that the function looks in to match the myDate parameter
then return the project name. Make sense? I'm not sure I'm being
clear.

 

[slabes]

I keep getting a #NAME? error with =myProjList(DATE(2006,9,1), "PRINT")

THoughts? Am I not compiling this right?

M.

 

[Dave Peterson]

Did you put the code in a General module of the same workbook's project?
Did you allow macros to run when you opened the workbook?

I keep getting a #NAME? error with =myProjList(DATE(2006,9,1), "PRINT")

THoughts? Am I not compiling this right?

M.

 

[slabes]

THe MAcro security was getting me. Now I'm getting a #VALUE? error?
Sorry I'm a little over my head with this I guess. I'm going to find
some intro to VBA stuff online and see if I can get a little closer
without driving you nuts with stupid questions.

M.

 

[Dave Peterson]

Depending on what you changed, it could be an error in the UDF.

THe MAcro security was getting me. Now I'm getting a #VALUE? error?
Sorry I'm a little over my head with this I guess. I'm going to find
some intro to VBA stuff online and see if I can get a little closer
without driving you nuts with stupid questions.

M.

 

[slabes]

OK. Some progress. The myCate parameter should be the column header
of the column that we match myDate in then returen the value from
column be of the matching row. So the Application.vLookup line is not
making sense to me. Why are we looking down the column for the myCate
value?

M

 

[Dave Peterson]

I didn't modify the =vlookup() stuff at all.

You have a column with Project Id's in it, right? Does that column change?

If not, then just drop the =vlookup() portion and assign it the column directly
in code:
to whatever you need.

If the project id column can change, how do you know what column to use?

This is the portion that does the real work:

If IsNumeric(.Cells(iRow, myCateCol).Value) Then
If CLng(.Cells(iRow, myCateCol).Value) _
= CLng(myDate) Then
'found a match
myStr = myStr & vbLf _
& .Cells(iRow, myProjCol).Value
End If
End If

It looks at the category column (header matches what's in the worksheet
formula). If that value is numeric, it could be a date, so do the comparison.

If the date in that cell matches the date you used in the worksheet formula,
then append it to the myStr variable.

OK. Some progress. The myCate parameter should be the column header
of the column that we match myDate in then returen the value from
column be of the matching row. So the Application.vLookup line is not
making sense to me. Why are we looking down the column for the myCate
value?

M

 

[slabes]

Now I see where we're talking past each other. I want to match the
date provided to the column specified and return the project id.
Project id's are alwasy in column B the different dates for each
project are in columns k through q with headers copy, approval, print,
ship, mail, final. I want to provide a date and a column like print,
and have all the project id's that have a print date that match the
provided one returned.

m

 

[Dave Peterson]

myProjCol = "A"
becomes
myProjCol = "B"

And dump that =vlookup() stuff completely.

And don't forget to change this line:
myCateRow = 1
to whatever row contains the categories.

Now I see where we're talking past each other. I want to match the
date provided to the column specified and return the project id.
Project id's are alwasy in column B the different dates for each
project are in columns k through q with headers copy, approval, print,
ship, mail, final. I want to provide a date and a column like print,
and have all the project id's that have a print date that match the
provided one returned.

m