Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with basic looping and criteria
I am trying to loop through a range and count the number of time a cell
contains the letters"CPCT". I am using the right function since they always occur at the end of the string. My cod keeps getting snagged he If xlApp.WorksheetFunction.Right(r1.Value, 4) = "CPCT" Then Any Ideas? My full code is below Dim r1 As Range Dim Count As Integer Count = 0 For Each r1 In xlApp.Sheets("Resource Info").Range("A4:A500") If xlApp.WorksheetFunction.Right(r1.Value, 4) = "CPCT" Then Count = Count + 1 End If Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with basic looping and criteria
Sub What()
Count = 0 Range("A4").Select Do Until ActiveCell.Address = "$A$500" If Right(ActiveCell.Value, 4) = "CPCT" Then Count = Count + 1 End If ActiveCell.Offset(1, 0).Select Loop End Sub "Jeff" wrote: I am trying to loop through a range and count the number of time a cell contains the letters"CPCT". I am using the right function since they always occur at the end of the string. My cod keeps getting snagged he If xlApp.WorksheetFunction.Right(r1.Value, 4) = "CPCT" Then Any Ideas? My full code is below Dim r1 As Range Dim Count As Integer Count = 0 For Each r1 In xlApp.Sheets("Resource Info").Range("A4:A500") If xlApp.WorksheetFunction.Right(r1.Value, 4) = "CPCT" Then Count = Count + 1 End If Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with basic looping and criteria
Try this...
Dim r1 As Range Dim Count As Integer Count = 0 For Each r1 In Range("A4:A500") If Right(r1.Value, 4) = "CPCT" Then Count = Count + 1 End If Next MsgBox "total = " & Count "Jeff" wrote: I am trying to loop through a range and count the number of time a cell contains the letters"CPCT". I am using the right function since they always occur at the end of the string. My cod keeps getting snagged he If xlApp.WorksheetFunction.Right(r1.Value, 4) = "CPCT" Then Any Ideas? My full code is below Dim r1 As Range Dim Count As Integer Count = 0 For Each r1 In xlApp.Sheets("Resource Info").Range("A4:A500") If xlApp.WorksheetFunction.Right(r1.Value, 4) = "CPCT" Then Count = Count + 1 End If Next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Looping for Criteria Checking | Excel Discussion (Misc queries) | |||
Looping Problem . . .Argh! | Excel Programming | |||
If Then Else looping problem | Excel Programming | |||
multiple looping criteria | Excel Programming | |||
Looping Problem | Excel Programming |