J
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.
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.
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.