Solved: Searching specific text in cells in a column

[shmilo]

Hi,

I've been trying to use the Excel VBA WorksheetFunction.Find or .Search functions in order to find if the text in a cell starts with the word Cust.
The problem I have is that when it finds the word it returns 1, but when it doesn't it gives the error "1004" - Unable to find the search property of the worksheetfunction class.

I used it as follows:
CellVal = Cells(i, 1)
rowCount = Application.WorksheetFunction.Search("Cust", CellVal)

What is wrong?

Thank you!

[Simon Lloyd]

Surely you would be better off using CountIf?

rowcount = Application.WorksheetFunction.CountIf(Range("A1:A100"), "Cust*")
MsgBox rowcount

[shmilo]

Thank you!!

The reason I'm trying to use Find or Search is that If I find "Cust" at the beginning of the text in a cell, I need to do something with this text, and remove this row.

With CountIf I can only count the times it appears in the column.

[mdmackillop]

Option Compare Text
Sub Test()
For Each cel In Intersect(Columns(1), ActiveSheet.UsedRange)
If cel Like "cust*" Then
cel.Interior.ColorIndex = 6
End If
Next
End Sub

[shmilo]

Thank you so much!

I'm sorry if I didn't sent all the info, but I'm new to this kind of Forum.

I should have posted the full information so you can send the best accurate answer.

The Sheet I'm working on can be of 10 to ~1000 lines.
It is divided into customers and rows of information about each of them.
See sample attached.

What I need to do is:
When finding in the first column the word Customer, I should take the next word (that is usually a number) and copy to a new sheet, named the same & 1 (if sheet name was trade, new sheet should be trade1) in cell(B2).
Then the rest of the text in the cell should be copied to cell(B3).
Then all the data below until next aperence of Customer, should be copied to cell(A4) and on.

I know how to do the rest, my problem was finding the text (that you showed me previously), but also how to strip the rest of the text as mentioned above.

Appreciate your help!!

[mdmackillop]

This doesn't appear to complicated but I don't follow your required result. Can you add a Results page based on your posted sample?

[shmilo]

Hi,

See attached the input (trade) and output (trade1) page.

Its like parsing the cell and using the second word and the rest.

Thanks!!

[mdmackillop]

Two options; look at Data/Text to Columns and the Split function

[shmilo]

Thanks!

I used TextToColumn to separate it. But then I need to delete the cell with "Customer:", and concatinate the cells after the number (in case the name is larger than 1 cell).

If no shorter possibilty, I will go with this solution.

Thanks a lot for all your help!!

[mdmackillop]

Option Explicit
Option Compare Text
Sub FixData()
Dim Nm As String, Cust As String, i As Long
Nm = ActiveSheet.Name
ActiveSheet.Copy after:=Sheets(Nm)
ActiveSheet.Name = Nm & 1
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) Like "cust*" Then
With Cells(i, 1)
Cust = .Value
.ClearContents
.Resize(2).EntireRow.Insert
.Offset(-1, 1) = Split(Cust)(1)
.Offset(0, 1) = Split(Cust)(2)
End With
End If
Next
End Sub

[shmilo]

Thanks!!!
It is fantastic.

Some questions:

1) Split (Cust)(1) means the second word of Cust? and so on....
2) Why you do the search from the end to the beginning?
3) Can you recommend on a good book that has all this excel vba possibilities explained? I'm working mainly with the "Help", but it is not enough.

Thanks again!!!

[mdmackillop]

1 Yes. Space is the default separator. You can define others within the function.
2. When deleting or adding rows, start from the end, otherwise the code skips over lines. Start with 1000 lines, code inserts 50 so from rows 1001 to 1050, the data will not be processed.
3. Check out our resources page for a start.

[shmilo]

Thanks!!