Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if the value of one cell can be found in a number of other c | Excel Programming | |||
check if a cell has date or a number | Excel Discussion (Misc queries) | |||
Check colour of number, then populate cell | Excel Worksheet Functions | |||
Check boxes in Excel: Not moving with cell... | Excel Discussion (Misc queries) | |||
Moving Check Boxes | Excel Discussion (Misc queries) |