Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HARALD STAFF - CONCAT_IF
Hi Harald
following on from our previous conversation would you like to test the following: --------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function ----------- Cheers JulieD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HARALD STAFF - CONCAT_IF
Hi Julie
It's beautiful ! Thanks for sharing this. Two minor adjustments only, first is Dim j as Long ;-) Then I'd make ConcRange optional, so that it behaves the same way as Sumif. Public Function CONCAT_IF(ConcCheck As Range, _ ConcCrit As Variant, _ Optional ConcRange As Range, _ Optional DelimitWith As String) As String 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long, j As Long Dim checkarray() As String Dim rangearray() As String If ConcRange Is Nothing Then Set ConcRange = ConcCheck i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function Best wishes Harald "JulieD" skrev i melding ... Hi Harald following on from our previous conversation would you like to test the following: --------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function ----------- Cheers JulieD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HARALD STAFF - CONCAT_IF
Hi Harald
thanks for this - good idea on the ConcRange and yes, dimming a variable would be considered "good programming" Cheers JulieD "Harald Staff" wrote in message ... Hi Julie It's beautiful ! Thanks for sharing this. Two minor adjustments only, first is Dim j as Long ;-) Then I'd make ConcRange optional, so that it behaves the same way as Sumif. Public Function CONCAT_IF(ConcCheck As Range, _ ConcCrit As Variant, _ Optional ConcRange As Range, _ Optional DelimitWith As String) As String 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long, j As Long Dim checkarray() As String Dim rangearray() As String If ConcRange Is Nothing Then Set ConcRange = ConcCheck i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function Best wishes Harald "JulieD" skrev i melding ... Hi Harald following on from our previous conversation would you like to test the following: --------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function ----------- Cheers JulieD |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HARALD STAFF - CONCAT_IF
Hi again Julie
What I never remember to set up in test scenarios is "nothing". Some day I'll learn. This is not good: Application.WorksheetFunction.CountA(ConcCheck) -clear a cell and the function returns nothing. Let us instead do i = ConcCheck.Count j = ConcRange.Count Best wishes Harald "JulieD" skrev i melding ... Hi Harald thanks for this - good idea on the ConcRange and yes, dimming a variable would be considered "good programming" Cheers JulieD "Harald Staff" wrote in message ... Hi Julie It's beautiful ! Thanks for sharing this. Two minor adjustments only, first is Dim j as Long ;-) Then I'd make ConcRange optional, so that it behaves the same way as Sumif. Public Function CONCAT_IF(ConcCheck As Range, _ ConcCrit As Variant, _ Optional ConcRange As Range, _ Optional DelimitWith As String) As String 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long, j As Long Dim checkarray() As String Dim rangearray() As String If ConcRange Is Nothing Then Set ConcRange = ConcCheck i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function Best wishes Harald "JulieD" skrev i melding ... Hi Harald following on from our previous conversation would you like to test the following: --------- Public Function CONCAT_IF(ConcCheck As Range, ConcRange As Range, ConcCrit As Variant, _ Optional DelimitWith As String) As String 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long Dim checkarray() As String Dim rangearray() As String i = Application.WorksheetFunction.CountA(ConcCheck) j = Application.WorksheetFunction.CountA(ConcRange) If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function ----------- Cheers JulieD |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HARALD STAFF - CONCAT_IF
Hi Harald
didn't think of testing it with nothing in it either :) so final(?!?!) function Public Function CONCAT_IF(ConcCheck As Range, _ ConcCrit As Variant, _ Optional ConcRange As Range, _ Optional DelimitWith As String) As String 'written by JulieD and Harald Staff 'ConcCheck - range to check for the criteria 'ConcRange - range to concatenation 'ConcCrit - the criteria 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long, j As Long Dim checkarray() As String Dim rangearray() As String If ConcRange Is Nothing Then Set ConcRange = ConcCheck i = ConcCheck.Count j = ConcRange.Count If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function ------- now all i need to do is update my website and put it on there - unless you have a better home for it? Cheers JulieD "Harald Staff" wrote in message ... Hi again Julie What I never remember to set up in test scenarios is "nothing". Some day I'll learn. This is not good: Application.WorksheetFunction.CountA(ConcCheck) -clear a cell and the function returns nothing. Let us instead do i = ConcCheck.Count j = ConcRange.Count Best wishes Harald ---snip--- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
HARALD STAFF - CONCAT_IF
Fine. Final until we find another bug.
I don't have a home for it yet, so put it onto your website, along with my initial Concat formula -they deserve to be together. Best wishes Harald "JulieD" skrev i melding ... Hi Harald didn't think of testing it with nothing in it either :) so final(?!?!) function Public Function CONCAT_IF(ConcCheck As Range, _ (...) ------- now all i need to do is update my website and put it on there - unless you have a better home for it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
new staff | Excel Worksheet Functions | |||
how many staff have 1 skill, how many staff have 2 skills, etc. | Excel Discussion (Misc queries) | |||
To: Harald Staff with Rgds to Your Code | Excel Programming | |||
To Harald Staff (or anyone who knows) | Excel Programming | |||
Thx CoRrRan, Bob Flanagan, Harald Staff | Excel Programming |