Problem with For Each loop
 

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

Problem with For Each loop
 

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" wrote:

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

Problem with For Each loop
 

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")

WLMPilot wrote:

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

--

Dave Peterson

Problem with For Each loop
 

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"WLMPilot" wrote:

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

Problem with For Each loop
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"WLMPilot" wrote in message
...
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

Problem with For Each loop
 

Thanks, that was the problem.

Les

"FSt1" wrote:

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" wrote:

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

Problem with For Each loop
 

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

"Dave Peterson" wrote:

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")

WLMPilot wrote:

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

--

Dave Peterson

Problem with For Each loop
 

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

Thanks again,
Les

"Barb Reinhardt" wrote:

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"WLMPilot" wrote:

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

Problem with For Each loop
 

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.)




WLMPilot wrote:

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

"Dave Peterson" wrote:

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")

WLMPilot wrote:

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

--

Dave Peterson


--

Dave Peterson