PDA

View Full Version : Query regarding worksheet function (Count IF) using in Excel VBA



gvreddyhr
05-05-2010, 07:26 AM
Hi,

I am not good in programming, but the clarity of the postings from the forum is making me to understand and implement to my work environment, now I have a query regarding worksheet function (Count IF) using in Excel VBA, I kindly request to help me in solving this

Query:-
I will be updating a data in sheet1 From column "A" to Column "Y",which I will be updating daily, cells contain a text, , I want to use a count if function in column “Z” up to the last row of the data updated in column “A” , formula should return the results like :

Z1 = countif(A1:Y1,”Text”)
Z2=Countif(A2:Y2,”Text”)
Z100=countif(A100:Y100,”Text”)

If i drag the formula, its occupies more storage and slowdowns the application, so kindly to help me in writing VBA code the above function.

Thanks in advance

Regards

GV Reddy

Bob Phillips
05-05-2010, 08:33 AM
Dim LastRow As Long

With Activesheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("Z1").Resize(LastRow)

.Formula = "=Countif(A1:Y1,""Text"")"
.Value = .Value
End With
End With

GTO
05-05-2010, 12:46 PM
If more rows, maybe...


Option Explicit

Sub exa()
Dim _
wks As Worksheet, _
rngLRow As Range, _
i As Long, _
REX As Object, _
aryResult() As Long

Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.Pattern = "Text" '<---Change to suit
End With

Set wks = ActiveSheet '<---Change to suit
With wks
Set rngLRow = RangeFound(.Range("A:Y"))
ReDim aryResult(1 To rngLRow.Row, 1 To 1)

For i = 1 To rngLRow.Row
aryResult(i, 1) = REX.Execute(Join(Application.Transpose( _
Application.Transpose( _
Range(.Cells(i, 1), .Cells(i, 25)) _
)), vbNullString) _
).Count
Next
.Range("Z1").Resize(UBound(aryResult, 1)).Value = aryResult
End With
End Sub

Function RangeFound(SearchRange As Range, _
Optional FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

Hope that helps,

Mark