Problem with UDF

[Jen_T]

I have received assistance with UDF and am having issues with tweaking the
module in VBA to what I need in various worksheets

Please see code and example below

The code that was written to provide the following in G2 if values below =
"Y", Note in any if the cells B through E ="Y" than "Priority" should be
indicated.

Ex: A2 (Y) B2 (Y) C2 (N) D2 (Y) E2 (Y) F2 (Y)

G2 = A, Priority, E, F

Code:
'The second range is optional If not specified it will return the column
'header...
'=Mstring(A2:F2)

'If specified it will return the header text
'=Mstring(A2:F2,A1:F1)

Function Mstring(myrange As Range, Optional myHeader As Range) As String
Dim cell As Range, blnPass As Boolean
For Each cell In myrange.Cells
If UCase(cell.Text) = "Y" Then
Select Case cell.Column
Case 2 To 4
If Not blnPass Then _
Mstring = Mstring & ", " & "Priority": blnPass = True
Case Else
If myHeader Is Nothing Then
Mstring = Mstring & ", " & Replace(Cells(1, _
cell.Column).Address(False, False), "1", "")
Else
Mstring = Mstring & ", " & myHeader(1, cell.Column)
End If
End Select
End If
Next
Mstring = Mid(Mstring, 3)
End Function


I would like to be able to add the UDF to lets say A2 or anywhere and then
look at these specific rows . Sometimes these values can be in other columns
and instead of moving data around I would prefer to update the UDF. I am
sure it is simple, any assistance would be great.
Second example:

Row 1 column headers
Row 2 - A2 (UDF)
DB2 (Y)
DC2 (Y)
DD2 (N)
DE2 (Y)
DF2 (Y)
DG2 (Y)
DH2 (N)
DI2 (Y)
DJ2 (Y)
A2 =
DC, Priority, DI, DJ
Note... If any of the cells through DC to DH equal "Y" than "Priority", If
none than do not indicate "Priority"
Also note that I will be pulling in column titles into A2, just using cell
address for example )DC, DI, or DJ.

 

[Jacob Skaria]

Try the below...

Function Mstring(myrange As Range, Optional myHeader As Range) As String
Dim cell As Range, blnPass As Boolean
For Each cell In myrange.Cells
If UCase(cell.Text) = "Y" Then
Select Case Split(Trim(Replace(cell.Address, "$", " ")))(0)
Case "A", "B"
If Not blnPass Then _
Mstring = Mstring & ", " & "Priority": blnPass = True
Case Else
If myHeader Is Nothing Then
Mstring = Mstring & ", " & Split(Trim(Replace _
(cell.Address, "$", " ")))(0)
Else
Mstring = Mstring & ", " & Cells(myHeader.Row, cell.Column)
End If
End Select
End If
Next
Mstring = Mid(Mstring, 3)
End Function

If this post helps click Yes

 

[Jacob Skaria]

Jen, missed to mention few things

--To try with your example replace
Case "A", "B"
with
Case "DC", "DD", "DE", "DF", "DG", "DH"
These are columns names which are to be excluded and to be assigned 'Priority'

So the function =mstring(DB2J2) with your sample data will return

DB, Priority, DI, DJ


--In the previous post you need to change the column number range (eg:2 to
4).. There was another bug which is also fixed...in the new UDF

If this post helps click Yes