Hi,

Suppose the cursor is on a certain row (say row 3 or 40), I want to
check if each cell from column B to column AF contains the number 1.
If it does, the counter i is incremented by 1. I want to do it by a
for next loop such that the column number is a variable. Can it be
done this way? Thank you.

Mike

You can loop through the columns.

Dim myRow as long
dim iCol as long
dim FirstCol as long
dim LastCol as long
dim myCount as long

with activesheet
firstcol = .range("B1").column 'I know that column B is 2
lastcol = .range("AF1").column 'I hate looking at column AF

myCount = 0
myrow = activcell.row
for icol = firstcol to lastcol
if .cells(myrow,icol).value = 1 then
mycount = mycount + 1
end if
next icol
end with

=======
Another way to do this is to use Excel's =countif() function:

Dim myRow as long
dim myCount as long

with activesheet
myrow = activcell.row
mycount = application.countif(.cells(myrow,1).range("b1:Af1"), 1)
end with



cyberdude wrote:
>
> Hi,
>
> Suppose the cursor is on a certain row (say row 3 or 40), I want to
> check if each cell from column B to column AF contains the number 1.
> If it does, the counter i is incremented by 1. I want to do it by a
> for next loop such that the column number is a variable. Can it be
> done this way? Thank you.
>
> Mike

--

Dave Peterson

ps.

Fix my typing error(s).

Activcell should be ActiveCell.

(There may be more!)

cyberdude wrote:
>
> Hi,
>
> Suppose the cursor is on a certain row (say row 3 or 40), I want to
> check if each cell from column B to column AF contains the number 1.
> If it does, the counter i is incremented by 1. I want to do it by a
> for next loop such that the column number is a variable. Can it be
> done this way? Thank you.
>
> Mike

--

Dave Peterson

Dave, suppose the cell value is 695173. Will it find the 1 ?

Mike F
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> ps.
>
> Fix my typing error(s).
>
> Activcell should be ActiveCell.
>
> (There may be more!)
>
> cyberdude wrote:
>>
>> Hi,
>>
>> Suppose the cursor is on a certain row (say row 3 or 40), I want to
>> check if each cell from column B to column AF contains the number 1.
>> If it does, the counter i is incremented by 1. I want to do it by a
>> for next loop such that the column number is a variable. Can it be
>> done this way? Thank you.
>>
>> Mike
>
> --
>
> Dave Peterson

Nope.

Did you want it to?

If yes, you could use:

If InStr(1, .Cells(myrow, iCol).Value, 1, vbTextCompare) > 0 Then
instead of:
if .cells(myrow,icol).value = 1 then


Mike Fogleman wrote:
>
> Dave, suppose the cell value is 695173. Will it find the 1 ?
>
> Mike F
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > ps.
> >
> > Fix my typing error(s).
> >
> > Activcell should be ActiveCell.
> >
> > (There may be more!)
> >
> > cyberdude wrote:
> >>
> >> Hi,
> >>
> >> Suppose the cursor is on a certain row (say row 3 or 40), I want to
> >> check if each cell from column B to column AF contains the number 1.
> >> If it does, the counter i is incremented by 1. I want to do it by a
> >> for next loop such that the column number is a variable. Can it be
> >> done this way? Thank you.
> >>
> >> Mike
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

The op wasn't too specific about that, but did say "contains the number 1".

Mike F
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Nope.
>
> Did you want it to?
>
> If yes, you could use:
>
> If InStr(1, .Cells(myrow, iCol).Value, 1, vbTextCompare) > 0 Then
> instead of:
> if .cells(myrow,icol).value = 1 then
>
>
> Mike Fogleman wrote:
>>
>> Dave, suppose the cell value is 695173. Will it find the 1 ?
>>
>> Mike F
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > ps.
>> >
>> > Fix my typing error(s).
>> >
>> > Activcell should be ActiveCell.
>> >
>> > (There may be more!)
>> >
>> > cyberdude wrote:
>> >>
>> >> Hi,
>> >>
>> >> Suppose the cursor is on a certain row (say row 3 or 40), I want to
>> >> check if each cell from column B to column AF contains the number 1.
>> >> If it does, the counter i is incremented by 1. I want to do it by a
>> >> for next loop such that the column number is a variable. Can it be
>> >> done this way? Thank you.
>> >>
>> >> Mike
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

If a cell holds: 23451234
Then it contains the numeral (or digit or character) 1.

But it doesn't contain the number 1.

(Just to be argumentative <vbg>.)



Mike Fogleman wrote:
>
> The op wasn't too specific about that, but did say "contains the number 1".
>
> Mike F
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Nope.
> >
> > Did you want it to?
> >
> > If yes, you could use:
> >
> > If InStr(1, .Cells(myrow, iCol).Value, 1, vbTextCompare) > 0 Then
> > instead of:
> > if .cells(myrow,icol).value = 1 then
> >
> >
> > Mike Fogleman wrote:
> >>
> >> Dave, suppose the cell value is 695173. Will it find the 1 ?
> >>
> >> Mike F
> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > ps.
> >> >
> >> > Fix my typing error(s).
> >> >
> >> > Activcell should be ActiveCell.
> >> >
> >> > (There may be more!)
> >> >
> >> > cyberdude wrote:
> >> >>
> >> >> Hi,
> >> >>
> >> >> Suppose the cursor is on a certain row (say row 3 or 40), I want to
> >> >> check if each cell from column B to column AF contains the number 1.
> >> >> If it does, the counter i is incremented by 1. I want to do it by a
> >> >> for next loop such that the column number is a variable. Can it be
> >> >> done this way? Thank you.
> >> >>
> >> >> Mike
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson