Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with UDF
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with UDF
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_T" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with UDF
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(DB2:DJ2) 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 --------------- Jacob Skaria "Jacob Skaria" wrote: 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_T" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |