Problem with For Each loop

[WLMPilot]

I have the following code:

begexprng = 0
Range("A" & begrng & ":A" & endrng).Select
For Each Cell In Selection
If Right(Cell.Value, 4) = "-Inc" Then
begexprng = begexprng + 1
Next Cell

I am trying to select a group of cells in column A and count the ones that
have
"-Inc". It should count any cell with either "1-Inc" or "2-Inc" There are
other values in column A also.

The problem is I am getting a compile error: Next without For.

Any ideas?

Thanks,
Les

 

[FSt1]

hi
you are getting a deceptive error. from your code i see you are missing an
end if
add that in and your next without for error should go away.
ie
For Each Cell In Selection
If Right(Cell.Value, 4) = "-Inc" Then
begexprng = begexprng + 1
End If
Next Cell

regards
FSt1

Regards
FSt1

 

[Dave Peterson]

You could use a worksheet function:

=countif(a1:a99,"*-inc")

You could use it in code, too:

dim BegExpRng as long
dim BegRng as long
dim EndRng as long

'test data
begrng = 33
endrng = 66

begexprng = application.countif _
(activesheet.range("a" & begrng & ":A" & endrng), "*-Inc")

 

[Barb Reinhardt]

You also may want to declare some variables if you haven't already. I'd
recommend adding

Option Explicit

Before your Sub ... line. This will force you to declare all variables.

 

[Don Guillett]

Missing an end if OR line continuation character
OR bring up to one line and you don't need selections

for each cell in Range("A" & begrng & ":A" & endrng)
If Right(Cell.Value, 4) = "-Inc" Then begexprng = begexprng + 1
Next Cell
msgbox begexprng

 

[WLMPilot]

Thanks, that was the problem.

Les

hi
you are getting a deceptive error. from your code i see you are missing an
end if
add that in and your next without for error should go away.
ie
For Each Cell In Selection
If Right(Cell.Value, 4) = "-Inc" Then
begexprng = begexprng + 1
End If
Next Cell

regards
FSt1

Regards
FSt1

 

[WLMPilot]

Thanks! the option you gave to use in a macro is what I needed. I took the
loop out.

Along the same thought process (not using a loop), is there a macro command
that will look at a range, say I5:I56, and if the cell.value = "P", I can
change it to "" (null)?

Thanks again,
Les

 

[WLMPilot]

Have already declared everything. Dave Peterson suggested an option that
worked great.

Thanks again,
Les

 

[Dave Peterson]

Record a macro when you select a range and do an Edit|Replace. Make sure you
change the settings you want--match case, look at the whole cell, ...

You'll end up with code like

Range("I5:I56").Select
Selection.Replace What:="p", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

You can drop the selection and use:

Range("I5:I56").Replace What:="p", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

(I dropped the SearchFormat and ReplaceFormat stuff. This stuff was added in
xl2002 (IIRC) and will break xl2k and below.)