Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a range
Is there anyway that I can apply this statement to the 1st to the 42n
cell without a for next loop? And .Cells(x, y).Offset(0, 1) < "IND" _ As you can see it checks to see if the value = "IND" in the first cell I also want to check the next 41 cells in that row for the sam criteria. So I want to see if "IND" is anywhere between 1 and 42 Thank -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a range
Why not use Find instead ? or would COUNTIF() be better
-- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a range
So I want to see if "IND" is anywhere between 1 and 42
And Application.Countif(.Cells(x,y).offset(0,1).Resize (1,41),"IND") 0 then if you want to know if they all contain IND, then change 0 to = 42 Seems like you have received this advice before. -- Regards, Tom Ogilvy "hotherps " wrote in message ... Is there anyway that I can apply this statement to the 1st to the 42nd cell without a for next loop? And .Cells(x, y).Offset(0, 1) < "IND" _ As you can see it checks to see if the value = "IND" in the first cell, I also want to check the next 41 cells in that row for the same criteria. So I want to see if "IND" is anywhere between 1 and 42 Thanks --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a range
I guess I did not realize that Resize would work for this also.
thought that Resize would actually place values in all of the cells not just count them. I used it before in a situation where I needed to complete a ro because the value fell short due to some other criteria and when I use Resize I thought it just forced the value in the remaining cells. Sorry for the similar pos -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a range
Resize itself does nothing. It expands the reference Range("A1") refers to A1 Range("A1").Value = 3 puts 3 in A1 Range("A1").Resize(1,3) refers to A1:C1 Range("A1").Resize(1,3).Value = 3 puts 3 in A1, B1, C1 application.Countif(Range("A1").Resize(1,3),3) is equivalent to Application.Countif(Range("A1:C1"),3) -- Regards, Tom Ogilvy "hotherps " wrote in message ... I guess I did not realize that Resize would work for this also. I thought that Resize would actually place values in all of the cells, not just count them. I used it before in a situation where I needed to complete a row because the value fell short due to some other criteria and when I used Resize I thought it just forced the value in the remaining cells. Sorry for the similar post --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select a range
I printed that explanation out, I'm sure I'll use it over and over.
Thanks To -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
I can't select a range in VBA | Excel Discussion (Misc queries) | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Select Sheet then Select Range | Excel Programming | |||
Select a range | Excel Programming |