VBA Excel: Advanced search

[amarty]

I'm new to VBA so I'm not sure how to do this.... Any help is appreciated......

Problem is the following: Passing trough one fixed column ("B") I need to find out how many (summarized) occurrences of LEFT(String, 2) there are.

Problem is the condition, because it's based on 60 cases - if LEFT(String, 2) is any of the following {"AA", "AB", "BR", "BN", "BZ", ... 60 conditions) counter increases.

Data in B column is something like AA2345GTE or BZHTR.

Thank you in advance!

 

[sous2817]

Hello and welcome to the board! Does it have to be VBA, or can you use a formula?

Here's a formula approach:

Excel Workbook
	A	B	C	D	E	F	G
1	List to check			Named range ("test")			Results:
2	aaples			aa			3
3	abunch			ab
4	crunch			ac
5	brakes			br
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(--(ISNUMBER(MATCH(LEFT(A2:A5,2),test,0))))

Excel Workbook
Name	Refers To
test	=Sheet1!$D$2:$D$5
Workbook Defined Names

Make a list of your conditions and create a named range (in my example, I called it 'test'). Column A is the list you'd like to count.

Let me know if that's close...

 

[amarty]

It must be VBA because column "B" are fill with data (Strings alike AA124G, ACR567, BN6789J, etc) where only first 2 letters could match any of 60 possible search criteria {AA, AN, AC, BP....}

My idea was something like this (but I don't know right VBA coding):



Sub AdvSearch60()

Dim i As Integer
Dim c As Integer
Dim Label As Variant

Range("B1").Select

Label = Mid(Selection.Value, 1, 2)

For i = 1 To end-of-excell-sheet

Label = Mid(Selection.Value, 1, 2)

Select Case Label

Case "AA"

c = c + 1

Case "AS"

c = c + 1

Case "AD"

c = c + 1

etc....


End Select
Next i

display-value-c

End Sub

 

[sous2817]

Did you try my suggestion? It's much cleaner than having to build out 60+ select case statements, and does exactly what you're asking (in my opinion at least). All you have to do is put the list you're searching for in some hidden, or out of the way place. That way, if your list ever changes, you don't have to modify the VBA you just have to adjust the list.

 

[amarty]

I have Excel 2002

 

[amarty]

I've tried as suggested (Excel 2007), I defined name test and entered formula... and I see error message "The formula you typed contains an error"......

I'm not into Excel, I have no clue what's the problem......

 

[GlennUK]

There is nothing wrong with the formula ... describe exactly what you are doing, in what cell, and exactly what formula you are trying to use. And tell what the definition is of test you have been using too.

 

[amarty]

First, I format columns "A" and "D" as Text:

• column "A": AA4567DX, AG56RTX, abH45, xx4567Gs, XX567dC, etc
• column "D": AA, AB, AC, XX, etc

After, I define name "test" for column "D" as: =Sheet1!$D$2:$D$61

Finally, I enter formula at G2 as: =SUMPRODUCT(--(ISNUMBER(MATCH(LEFT(A2:A5000,2);test;0))))

 

[GlennUK]

You have a mixture of comma and semi-colon as separators in your formula. Make them all be whatever your installation separator character should be.

 

[amarty]

Now it works!!!!!!!

sous2817 'n' GlennUK: Thank you VERY much!