How to check if the cell contains number 1 by moving down thecolumns?

[cyberdude]

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]

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

 

[Dave Peterson]

ps.

Fix my typing error(s).

Activcell should be ActiveCell.

(There may be more!)

 

[Mike Fogleman]

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

Mike F

 

[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]

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

Mike F

 

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

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

Mike F