VBA: Help me write a code in vba

[shrini007]

I want to count 'a' (a++) if it satisfies following criteria:

for each cell in range b4 to last used row and column:

1) value of cell in column B should not be equal to "TEXT"

2) value of corresponding cell in column M should not be blank

3) corresponding value of cell in column G should match with elements of following array:

array= {d,e,f,g,h}

Can someone tell me how to do this in VBA?
Thank u so much in advance.

[snb]

Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble (http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584)

[Fennek]

Hi,

try this code:



Sub Fen()
lr = Cells(Rows.Count, "A").End(xlUp).Row

Ar = Range("A1:M" & lr)
With CreateObject("vbscript.regexp")
.Pattern = "d|e|f|g|h"
For i = 1 To UBound(Ar)
If Ar(i, 2) = "Text" Then Ar(i, 1) = ""
If IsEmpty(Ar(i, 13)) Then Ar(i, 1) = ""
T = .test(Cells(i, "G"))
If T Then Ar(i, 1) = ""
Next i
.Global = True
.Pattern = "a"
Tx = Join(Application.Transpose(Application.Index(Ar, 0, 1)), ", ")
Set RR = .Execute(Tx)
MsgBox RR.Count
End With
End Sub



It is a collection of codes I found in several forums.

regards

[offthelip]

To my brain this is a much simpler way of doing it:

lr = Cells(Rows.Count, "A").End(xlUp).Row
aCount = 0
Ar = Range("A1:M" & lr)
For i = 1 To lr
If Ar(i, 2) = "Text" Then
If IsEmpty(Ar(i, 13)) Then
If (Ar(i, 7) = "d" Or Ar(i, 7) = "e" Or Ar(i, 7) = "f" Or Ar(i, 7) = "g" Or Ar(i, 7) = "h") Then
aCount = aCount + 1
End If
End If
End If
Next i
MsgBox aCount

[Bob Phillips]

To my brain this is a much simpler way of doing it:

As is this



lr = Cells(Rows.Count, "A").End(xlUp).Row aCount = ActiveSheet.Evaluate("SUMPRODUCT((B1:B" & lr & "=""Text"")*" & _
"(G1:G" & lr & "={""d"",""e"",""f"",""g"",""h""})*" & _
"(M1:M" & lr & "=""""))")
MsgBox aCount

[snb]

I'd prefer


If (instr(" d e f g h "," " & Ar(i, 7) & " ") Then

or

Sub M_snb()
Msgbox [sum(if(G1:G30000<>"",istext(B1:B3000)*(code(G1:G3000)>99)*(code(G1:G3000)<105),0))]
End Sub