How to check if the cell contains number 1 by moving down thecolumns?
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 |
How to check if the cell contains number 1 by moving down thecolumns?
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 |
How to check if the cell contains number 1 by moving down thecolumns?
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 |
How to check if the cell contains number 1 by moving down the columns?
Dave, suppose the cell value is 695173. Will it find the 1 ?
Mike F "Dave Peterson" wrote in message ... 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 |
How to check if the cell contains number 1 by moving down thecolumns?
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" wrote in message ... 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 |
How to check if the cell contains number 1 by moving down the columns?
The op wasn't too specific about that, but did say "contains the number 1".
Mike F :) "Dave Peterson" wrote in message ... 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" wrote in message ... 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 |
How to check if the cell contains number 1 by moving down thecolumns?
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" wrote in message ... 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" wrote in message ... 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 |
All times are GMT +1. The time now is 11:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com