HARALD STAFF - CONCAT_IF

  • Thread starter Thread starter JulieD
  • Start date Start date
J

JulieD

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
 
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
 
Hi Harald

thanks for this - good idea on the ConcRange and yes, dimming a variable
would be considered "good programming"

Cheers
JulieD
 
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
 
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 said:
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
Click to expand...
---snip---
 
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
 
Hi Harald

with more testing i've discovered that you need to use named ranges to
access ranges on other worksheets ... but i've just added that into the
comments :)

i've uploaded both the CONCAT_RANGE and CONCAT_IF functions to my web site
with brief notes on using - check it out at
www.hcts.net.au/tipsandtricks.htm

and i've decided that i'm going to lose the purple in the next version of my
site!

Cheers
JulieD


Harald Staff said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

InSTR Compare erorr 3
Code works but is slow except when.... 4
Email with excel 1
Blank item in listbox, how to remove 3
userinterfaceonly=True fails with this code! 1
list box remove item by combobox 4
Combobox not scrolling 1
Array range error question 1

Back
Top