VBA Help

[decadence]

Hi, Is it possible to count comma delimited text in a column and return the value in the next empty column but do so based on the header found. (In this instance References to Find is the Header)

Example
[TABLE="class: grid, width: 25, align: left"]
<tbody>[TR]
[TD]References to find[/TD]
[TD]Quantity Found[/TD]
[/TR]
[TR]
[TD]A1,A2,A3,A4,A5,A6,A7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]B11,B9,B20,B333[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]C1,C123-1,C69_2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]D1,D7,D11[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

This will work with the data you posted assuming the data is in Columns A and B:

Sub SplitData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("A2:A" & LastRow)
        rng.Offset(0, 1) = UBound(Split(rng, ",")) + 1
     Next rng
    Application.ScreenUpdating = True
End Sub

You will have to give us more information on what you mean by:

based on the header found

 

[Johnny C]

easy with VBA: insert this into a module

Option Base 1
Public Function CountPiece(str_text$, str_Delim$) As Long

    Dim arrCheck    As Variant
    Dim l_counter   As Long
    
    arrCheck = Split(str_text, str_Delim)
    CountPiece = UBound(arrCheck) + 1

End Function

Then to use it just type =CountPiece(A1,",") where A1 is the cell and the comma is the delimiter

 

[decadence]

Hi mumps, what I mean is If the header "References to Find" is found then count the comma delimited text in that column and return the total found in the next empty column if header not found then exit.

Another Example:

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]References to find
[/TD]
[TD]Something
[/TD]
[TD]Something Else
[/TD]
[TD]Not Something
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1,A2,A3,A4,A5,A6,A7
[/TD]
[TD][/TD]
[TD]pie
[/TD]
[TD]yes
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]B11,B9,B20,B333
[/TD]
[TD]banana
[/TD]
[TD][/TD]
[TD]maybe
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]C1,C123-1,C69_2
[/TD]
[TD]carrot
[/TD]
[TD]cake
[/TD]
[TD]no
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]D1,D7,D11
[/TD]
[TD][/TD]
[TD][/TD]
[TD]tbc
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Johnny C]

Create a new file, Open VBA and put the function above

Option Base 1
Public Function CountPiece(str_text$, str_Delim$) As Long

    Dim arrCheck    As Variant
    Dim l_counter   As Long
    
    arrCheck = Split(str_text, str_Delim)
    CountPiece = UBound(arrCheck) + 1

End Function

Now save it as type '.xlam' - this creates an Add-in file. Then go to your Add In manager and Browse to the file you just saved and enable it.

Then in either your personal.xlsb or the workbook, put this:

Sub PutFunct()

LastCol = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
For ColCount = 1 To LastCol
    If ActiveSheet.Cells(1, ColCount).Value = "References to find" Then
        ThisCol = Replace(ActiveSheet.Cells(1, ColCount).Address(False, False), "1", "")
        For RowCount = 2 To LastRow - 1
            ActiveSheet.Cells(RowCount, LastCol + 1).Formula = "=CountPiece(" & ThisCol & RowCount & ", "","")"
        Next RowCount
    End If
Next ColCount
End Sub

Tihs will add a formula for each row in the column after the last one used in the worksheet that calls the function you added to the Add in.

 

[decadence]

Hi Johnny C, I am getting #NAME ? in the cells.

 

[Johnny C]

Hi. Try inserting a function, select user-defined, can you see the function you created in the xlam addin?

 

[decadence]

Thanks Johnny C it works now that I have remembered to put the function in the .xlam module instead of a workbook personal.xlsb module. Thanks Again

 

[Johnny C]

Goodo