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

  • Thread starter Thread starter cyberdude
  • Start date Start date
C

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
 
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
 
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
 
The op wasn't too specific about that, but did say "contains the number 1".

Mike F :)
 
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 said:
The op wasn't too specific about that, but did say "contains the number 1".

Mike F :)
Click to expand...
 
Back
Top